Sheetname in formula to get value in cell on that sheet

[Hudco]

Hi, I have a workbook with sheet1 where cell say A1 has the name of a sheet in the workbook. On say sheet4 I want a formula that can reference the sheet name on sheet1 and get the data from say cell B5.
That is the formula in plain English would be "Sheet1 A1 cell B5"
I have tried

=INDIRECT("'"&Sheet1A1&"'!B5",)

which will give the data at Sheet4 B5 however if I copy the formula to the next cell down so I can get the data in B6 it retains "B5".
Any suggestions?
Thanks

 

[Joe4]

You can use the ROW() function, which returns the row number of the current cell that the formula is placed in.
So, as you copy that down a column into other rows, that value increases also.

Let's say that your originally formula (that is returning B5) is being placed in row 2. So the row number in the formula is 3 more than the row number the formula is being placed in.
Then, you could write it like:

=INDIRECT("'" & Sheet1!A1 & "'!B" & ROW()+3)

 

[Hudco]

You can use the ROW() function, which returns the row number of the current cell that the formula is placed in.
So, as you copy that down a column into other rows, that value increases also.

Let's say that your originally formula (that is returning B5) is being placed in row 2. So the row number in the formula is 3 more than the row number the formula is being placed in.
Then, you could write it like:

[COLOR=#333333]=INDIRECT("'" & Sheet1!A1 & "'!B" & ROW()+3)[/COLOR]

Hi Joe4,
Thanks for the quick response. The formula works but the first row in the result sheet is the same as the row in the target sheet. Obviously I have changed the "3" to 1 but that then gives the data from the row below.

My workaround is to start with the row above (specify in the formula) and then use your formula which works as designed.

Thanks,

Clyde