I have a project that requires me to copy mutiple worksheets to one hub
worksheet. Is there a way to make the worksheet name a variable?
This is what i have so far:

=IF(Period8224a!$R$9=$C$2,Period8224a!$N$9,0)

The Period8224a name will change like 50 to 100 times. If there was a way
to reference the name of the sheet from a cell on the hub sheet like this:

=IF("X1"!$R$9=$C$2,"X1"!$N$9,0)

with the "X1" cell containing the worksheet name.

You can use the €œIndirect€� function to get the name of the worksheet into a
formula. Assume the name of the worksheet is in cell A9, this formula should
do what you want:

=IF(INDIRECT((A9)&"!$R$9")=$C$2,INDIRECT((A9)&"!$N $9"),0)

Tom

=if(indirect("'"&x1&"'!r9")=$c$2,indirect("'"&x1&" '!n9"),0)

The apostrophes are not always required. If you included them and excel doesn't
need them, it's not a problem.

But if you don't include them and excel needs them, excel will yell.

And since the stuff inside the =indirect() is a string, I didn't need to use the
$ in the address (for R9). It won't change when that formula is copied to other
cells.



ThunderBlade wrote:

I have a project that requires me to copy mutiple worksheets to one hub
worksheet. Is there a way to make the worksheet name a variable?
This is what i have so far:

=IF(Period8224a!$R$9=$C$2,Period8224a!$N$9,0)

The Period8224a name will change like 50 to 100 times. If there was a way
to reference the name of the sheet from a cell on the hub sheet like this:

=IF("X1"!$R$9=$C$2,"X1"!$N$9,0)

with the "X1" cell containing the worksheet name.

--

Dave Peterson

Thank you both very much. I had never used the Indirect Function before.
Everything is working as planned.