Series Fill for sheet number
Series Fill for sheet number
(OP)
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
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





RE: Series Fill for sheet number
RE: Series Fill for sheet number
So you could start in row 1 as follows:
A1: =2
B1: =A1+1
C1: =B1+1
and so forth (by copying B1 to the right as far as you need)
Then, in row 2 generate the address text:
A2: ="Sheet"&A1&"!$B$5"
B2: ="Sheet"&B1&"!$B$5"
and so forth (by copying B2 to the right as far as you need)
Then, retrieve the values in row 3:
A3: =INDIRECT(A2)
B3: =INDIRECT(B2)
and so forth (by copying B3 to the right as far as you need)
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.