Series Fill for sheet number 1

[BJC]

I want to fill in a row where each column goes to a different worksheet and gets the value in cell B5

The follwoing would be the first 4 cells in a row on a work sheet. The first sheet is linked to following sheet to get values for data in row.

Sheet2!$B$5 Sheet3!$B$5 Sheet4!$B$5 Sheet5!$B$5

I can type this manually but want to know if there is a way to drag the first cell and have the diget after the sheet number to fill automatically like it does when yo do a fill series operation.
I have about 40 sheets and have already spent enough time to do it manually. I am asking to learn a new trick and for the next time.
Thank you
BJC

 

[MintJulep]

Some combination of INDIRECT(), ROW() and COLUMN() functions should get you there.

 

[joerd]

There is a little problem with accessing a sequence of sheets. So you'll have to generate the address as a text string, then use INDIRECT to convert the text to a valid reference that Excel can use.
So you could start in row 1 as follows:
[tt]
A1: =2
B1: =A1+1
C1: =B1+1
and so forth (by copying B1 to the right as far as you need)
[/tt]
Then, in row 2 generate the address text:
[tt]
A2: ="Sheet"&A1&"!$B$5"
B2: ="Sheet"&B1&"!$B$5"
and so forth (by copying B2 to the right as far as you need)
[/tt]
Then, retrieve the values in row 3:
[tt]
A3: =INDIRECT(A2)
B3: =INDIRECT(B2)
and so forth (by copying B3 to the right as far as you need)
[/tt]

You can combine a few formulas if you like. I hope it works, haven't tested it.

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.