Reference to a tab

  • Thread starter Thread starter Andy B
  • Start date Start date
A

Andy B

Hi all

I am trying to collect values from a sheet (say, AL). In A2 I have AL and I
am trying to use that to reference the sheet AL. I've tried
=INDIRECT(A2)&"!A2", and many other ways around it. If I change AL to BU, I
want the formula to then use sheet BU.
Thanks in advance.

Andy
 
You need to put single quotes around the sheet name, so:
=COUNTA(INDIRECT("'"&A10&"'!A:A"))-1

Here's the formula again with spaces just so you can read it - but these
spaces shouldn't be there in the real thing:
=COUNTA(INDIRECT(" ' "&A10&" ' !A:A"))-1
 
Harald and Andy:
I'm not sure that the modified formula would work either. You have to
have two arguments with that function; the range and criteria. The
range is there ("INDIRECT(A10&"!A:A")") however there criteria is
missing. The "-1" would be considered part of the range but wouldn't
it return an error as an invalid range? You might try this formula:
=COUNTIF(INDIRECT(A10&"!A:A"),"<>""")-1

I'm not sure what the -1 was suppose to be for other than decrementing
the result by 1. If that's true then this formula should work but be
careful. A "0" result on the COUNTIF would create a "-1" for the
formula. You also need to be careful to include a range that reviews
only data and not column headers when using this formula. I haven't
test the formula fully but i would bet that it would pick up a label
as being not equal to "" (<>"") and count it as 1.
 
OP wasn't using COUNTIF, but COUNTA. And he explained exactly what he was
trying to do: "I am trying to count the number of cells used in column A,
excluding the header."
 
Back
Top