Using text form 1 cell in a formulae

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
Try
=COUNT(INDIRECT(a1&"!A:A"))

count requires a value list which must be a list of
values, or ranges, but can't be a string formula which
results in values or ranges.

Using Indirect allows you to concatenate a string and
have Excel recognise that string as a Range reference.

Steve
-----Original Message-----
Right this is an anoying little beggar but probably
Click to expand...
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
Click to expand...
room") and other similair formulae
my question is this
In column A i have the name of the sheet so I.E.
Click to expand...
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
Click to expand...
Count formula needs automatically?
 
Thats works But......

If a space in the text field you need to put it like this
=COUNT('AB Electronics'!A:A) note the two ' either side of the company name!

if you use replace with
=COUNT(INDIRECT(A9&"!A:A")) then this doesn't work and just returns a 0 value!

any sugestsions other thna adding in the two ' on the company name as this would just look untidy!

thanks for the sugestion tho
 
Add the single quotes to the indirect formula:

=COUNT(INDIRECT("'" & A9 & "'!A5:A20"))
 
sorry it does work i copied you formulae into my sheet and hadnt noticed you changed the range from A:A to A5-A20 hence the 0 resulT
 
nope still returns a 0 value!

nice try tho ive tried having a fiddle with it and no mater how i phrase it i still get a 0 value!
 
Ok now wwe got that to work
i tried ot use the same thing on a countif formulae but it says it contiants errors i cant spot them unless its just a case of a icant use the indirect variable with COUNTIF

my formulae

=COUNTIF(INDIRECT("'"&A2&"'!E:E,""Neg room")
this is designed to count how many times it says Neg room in column E
my orignial version
=COUNTIF('A2Z-UK'!E:E,"Neg room"

I says to few arguments entered but i cant see what I am missing!
 
Back
Top