Hi all,

I am trying to make a formula which will use values off of over 40 different sheets in the same workbook. I have made the SheetName macro and wish to use the name that this macro gives me inside other functions such as:
Example:
SheetName(1) = Sheet1
COUNTIF(SheetName(1)!A1:A250,crtieria)

This returns an error. Can anyone help me solve this issue? The major problem here is the name of each sheet will differ on a regular basis as this is for a weekly report generation.

Thank you