Copy Tab Names Down 99 Rows

[jma77]

On a summary page, each row represents data from a corresponding tab. Column A, starting in A3, references tab1, $c$5 to return the name of an area. The formula is: =Area1!$c$5

I want to drag that formula down 99 rows for each of the tabs: Area 2, Area 3, etc, all the while referencing $c$5 on each of those sheets.

I also have a formula set up that when a name is entered into C5 of a sheet, it changes the tab name to what is in C5, so maybe there is a way to reference the tab name only when doing the drag down?

 

[Joe4]

Welcome to the Board!

You should be able to use the INDIRECT function to dynamically build your sheet reference.
See here for details: Excel formula: Indirect named range different sheet | Exceljet

 

[jma77]

Welcome to the Board!

You should be able to use the INDIRECT function to dynamically build your sheet reference.
See here for details: Excel formula: Indirect named range different sheet | Exceljet

Thank you! I am not sure if that solves my issue. I do use indirect for another section of the data, but I am no sure the link led me to what I need.

I think I have a better way to explain what I am after:

Presently, my summary page has two header rows and 15 rows underneath that represent 15 different areas and a pull through of each of the areas costs.

Each of the data sheets is an identical template and the sheetname is the area name.

$C$5 on each data sheet is simply the area name (which is the same as the sheetname).

Say I were to add another sheet for a new area at the end and call it Area16

I would then have to insert a corresponding row on the summary page.

Presently, A17's formula is: =Area15!$C$5 (again, just to return the area name).

If I were to insert a row between row 17 & 18 and drag that formula down from row 17, it would still say =Area15!$C$5

I can manually change it to say =Area16!$C$5

But because I have to insert/add 75 rows on the summary page and corresponding data sheets, that is time consuming to do manually. I need to find a way to either drag the formula down that changes the 15 to a 16, then 16 to a 17, then 17 to an 18, etc in a series or get it so the cells in column A reference the sheetname (with the ability to drag the formula down so I don't have to do the MID FILENAME formula in each row of column A).

 

[Joe4]

Presently, A17's formula is: =Area15!$C$5 (again, just to return the area name).

So, if the sheet number (15) is always 2 less than the row number 17, you could do this, which would work when you drag the formula down.
=INDIRECT("Area" & ROW()-2 & "!$C$5")

They key is the part in red. It takes the current row number (the row in which the formula resides) and subtracts 2.
So when you copy it down to row 18, that becomes 16.