# Sheetname in formula to get value in cell on that sheet

#### Hudco

##### Board Regular
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

Last edited:

### Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
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:
Rich (BB code):
``=INDIRECT("'" & Sheet1!A1 & "'!B" & ROW()+3)``

Last edited:
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:
Code:
``[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

Replies
6
Views
216
Replies
0
Views
90
Replies
9
Views
357
Replies
4
Views
97
Replies
1
Views
161

1,203,600
Messages
6,056,206
Members
444,850
Latest member
dancasta7

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back