Referencing sheet name on formula

[fawnlemur]

Hi

I have a list of current sheet names, could differ fro day to day (as a Macro creates sheets and updates the list:

e.g.

A B
1 | 12345 |
2 | 23456 |
3 | 34567 |

Now on the B column I want to add the formula to say if A1 = one of the sheet name then on that sheet count how many entries are on I:I.

on each sheet C1 equals the sheet name ( the macro that creates the sheets gets it from there).

this I the formula I tried:

=if(A2&"!C1"=A2,CountA(A2&"!I:I),"Not working")

And it always comes back as not working.

anyone know any other way to fix this.

Thank you

 

[Eric W]

Re: Referencing sheet name on formula - Need Help!

Maybe:

=IFERROR(IF(A2=INDIRECT("'"&A2&"'!C1"),COUNTA(INDIRECT("'"&A2&"'!I:I"))),"Sheet not found")

 

[fawnlemur]

Re: Referencing sheet name on formula - Need Help!

Thank you, if possible can you please explain how this formula works as I've never used indirect before.

Thanks

 

[Eric W]

Re: Referencing sheet name on formula - Need Help!

INDIRECT just converts a string representation of a range into an actual range that the formulas can use. So if A2 has a sheet name, like 123, then "'"&A2&"'!C1" becomes '123'!C1 which is a standard range address. INDIRECT takes that and converts it into an actual range. I used single quotes around the sheet name in case you have spaces in the sheet name, but if not, you can do without them. If the range does not exist, (like if the sheet name doesn't exist), INDIRECT returns a #REF error, and the IFERROR function captures that and returns "Sheet not found". The other INDIRECT creates a range the same way, and the range is given to COUNTA.

Let me know if you have any other questions.

 

[fawnlemur]

Re: Referencing sheet name on formula - Need Help!

Thank you. I've learned something new