# Copy Tab Names Down 99 Rows

#### jma77

##### New Member
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?

### Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

#### jma77

##### New Member
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.

Replies
1
Views
245
Replies
5
Views
54
Replies
1
Views
73
Replies
20
Views
167
Replies
21
Views
348

1,126,957
Messages
5,621,823
Members
415,859
Latest member
Vain

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