Change Sheet but not cell reference when dragging down formula

[Skycop]

Hello all, I have tried searching for what I believe to be a simple answer, but I can't seem to find what I am looking for. I have a very simple spreadsheet where I have sheets labeled 1-50. I then have a summary page which references different cells on each sheet. ='1'!$F$2 for example.

What I am trying to do is when I drag down through all 50 cells to get the cell reference to change accordingly where on row 2 it would read: ='2'!$F$2

I have tried numerous times and am at my wits end. Any help would be greatly appreciated. Not only does this need to be repeated for the 50 rows, but also needs to be repeated for different columns, but I think I can figure it out from there if someone points me in the right direction. Thank you very much.

 

[CraigWM]

Try this formula:

=INDIRECT("'"&ROW()&"'!$F$2")

As long as your row in the summary sheet is the same number as the sheet name you want to reference (as said in your example), it should work. If not, you can place the sheet name in a column in the summary sheet and instead of ROW(), refer to that cell.

 

[Domenic]

Let's say that the formula is going to be entered in B2 and copied down, try...

B2, copied down:

=INDIRECT("'"&ROWS($B$2:B2)&"'!F2")

Change ROWS($B$2:B2) according to your starting cell. Also, if you want to be able to copied the formula across as well, try...

B2, copied down and across:

=INDIRECT("'"&ROWS(B$2:B2)&"'!"&CELL("address",F$2))

 

[Skycop]

I don't think I explained myself well enough. The data is being displayed on the summary page, which is NOT the same cell location as the data is being derrived from. For example, On my summary sheet, cell F2 will have the data from Sheet 1 B2 displayed. The next row will need to have data from Sheet 2 B2 and so forth.

 

[Domenic]

I don't think I explained myself well enough. The data is being displayed on the summary page, which is NOT the same cell location as the data is being derrived from. For example, On my summary sheet, cell F2 will have the data from Sheet 1 B2 displayed. The next row will need to have data from Sheet 2 B2 and so forth.

Try...

F2, copied down:

=INDIRECT("'"&ROWS($F$2:F2)&"'!B2")

 

[Skycop]

I think I am making this more difficult than it needs to be. However, I can't get these forumulas to work (more than likely user error). I appreciate the help thus far.

In reference to my original post, I would like cell B4 of my summary page to reference to a sheet titled "1" and have cell A2 displayed from sheet 1.

Then, when I scroll down on the summary page, I would like cell B5 to display Sheet 2 cell A2 and so on. Could someone give me a formula to input with these cells? I am not sure where to put the data in the forumulas provided. Your help is appreciated and thanks for bearing with me.

 

[CraigWM]

In cell B4, B5, etc. enter =INDIRECT("'"&ROW()-3&"'!$A$2")

Use the formula auditing Evaluate Formula to step through the formula to understand how this works and how it can be changed to suit your next needs.

Your example says you want to lookup from a sheet named a number that is 3 less than the row that the cell is on in the Summary page and you are always referring to cell A2 in that referenced sheet - (B4 of my summary page to reference to a sheet titled "1" and have cell A2, cell B5 to display Sheet 2 cell A2).

This is an unstable configuration. One change to any of the sheets will cause the Summary sheet to break (not reference the correct cells). A change such as someone inserting/deleting a column/row will cause the problems. A better long term solution may be to use lookups (vlookup, hlookup, index/match, summproduct, sumif, etc.) that will give your spreadsheet some resiliency as time marches on, changes are introduced or another person is responsible for the sheet. I do not know your spreadsheet so that may not be easily done though.

 

[Domenic]

The formula I offered can be adjusted as follows...

B4, copied down:

=INDIRECT("'"&ROWS(B$4:B4)&"'!A2")

...which would be robust against inserting one or more rows at or before Row 4.

 

[Skycop]

Both of these worked quite well. I appreciate everyone's help more than you know. I am going to spend some time with the Formula Evaluator to see what I can learn from this situation (as CraigWM suggested). In reference to the spreadsheet changing, this has been my final revision and is actually a flight log that I carry in the ****pit that gets transferred to electronic format. The spreadsheets shouldn't change a whole lot, although the summary page is a work in progress, however, I see where the data references are coming from now. Also, with very few exceptions on this spreadsheet, the data is just a transfer of data that was originally done on the flight log itself to the summary page. The flight logs are where the formulas (albeit very simple ones) were conducted.

 

[Burichel]

Hi, I just want to understand whats behind that formula and why does it work? What does "'" mean?