Concatenate Formula from Text

[adakos]

Hi.
My problem is this:
I Have a list of sheets in a workbook named 1-31 representing each day of the current month. I also have a central time calculation sheet.

I use ='1'!Q5 to pull Q5 From the sheet named "1" which represents total hours worked in a given day.

When I use "fill down" to fill out the rest of the month, I get the following:
='1'!Q5,
='1'!Q6,
='1'!Q7,
='1'!Q8

And I need this:
='1'!Q5,
='2'!Q5,
='3'!Q5,
='4'!Q5

I've tried:
=CONCATENATE("='"&ROW()-1&"'!"&"Q5")
and
="='"&ROW()-1&"'!"&"Q5"

But I end up with text that says:
='1'!Q5,
='2'!Q5,
='3'!Q5
... but not a formula.

Any suggestions?

-Adakos

 

[schielrn]

Try:

=indirect("'"&ROW()-1&"'!"&"Q5")

Note for future reference, that indirect cannot be used with closed workbooks, but that does not seem like it will be a problem here.

You may also want to fill row with a cell reference to ensure you get the correct results and you do not need to subtract:

=indirect("'"&ROW(A1)&"'!"&"Q5")
Hope that helps.

 

[Jonmo1]

Welcome to the board...

You need INDIRECT

Try
=INDIRECT("'" & ROW() & "'!Q5")

 

[adakos]

Thanks for the help.

It works perfectly!