Using a txt string in formulae

[James]

Right this is an anoying little beggar but probably simple and im failing to see the wood from the trees so to speak !

right i have a workbook with multiple sheets and a front sheet

the front sheet reports results of counts from the other sheets
using =COUNT(sheet1!A:A) and =COUNTIF(Sheet1E:E,"Neg room") and other similair formula

my question is this
In column A i have the name of the sheet so I.E. A1=sheet1 which hyperlinks to the named sheet
and columb B has the first formulae and so on and so on!
my question is

is there a way i can add the Word name of the sheet the Count formula needs automatically

Ie if sheet1 was called Chicken

A1 would say chickens with a hyper lin
B1 needs to say =COUNT(chickens!A:A

is there a string or value i can put in to so i it can say something like
=COUNT((a1)!A:A)
although that Formula doesn't wor

My apologies for my punctuation and typing!

I hope someone can hel

Best regards
James

 

[Paul]

Right this is an anoying little beggar but probably simple and im failing

to see the wood from the trees so to speak !

right i have a workbook with multiple sheets and a front sheet

the front sheet reports results of counts from the other sheets
using =COUNT(sheet1!A:A) and =COUNTIF(Sheet1E:E,"Neg room") and other similair formulae

my question is this
In column A i have the name of the sheet so I.E. A1=sheet1 which hyperlinks to the named sheet!
and columb B has the first formulae and so on and so on!
my question is

is there a way i can add the Word name of the sheet the Count formula needs automatically?

Ie if sheet1 was called Chickens

A1 would say chickens with a hyper link
B1 needs to say =COUNT(chickens!A:A)

is there a string or value i can put in to so i it can say something like
=COUNT((a1)!A:A)
although that Formula doesn't work

My apologies for my punctuation and typing!

I hope someone can help

Best regards
James

Try this:
=COUNT(INDIRECT(A1&"!A:A"))

 

[Steve Farrar]

Put the worksheet name in cell A1 and try this formula

=COUNT(INDIRECT($A1&"!"&"a:a"))

 

[Dave Peterson]

If your worksheet names can have spaces, it's good to have the single quotes
around the worksheet name.

If they aren't needed, then excel won't care. But if they are needed and you
don't have them, you'll have a problem.

=COUNT(INDIRECT("'"&A1&"'!A:A"))

And if you make a typo in A1, that formula will hide the error. It evaluates to
=count(#ref!) and that evaluates to 0.

Maybe testing for an error would be safer:
=IF(ISERROR(INDIRECT("'"&A1&"'!a:a")),"No sheet available",
COUNT(INDIRECT("'"&A1&"'!A:A")))
(all one cell)