Copy Tab Names Down 99 Rows

jma77

New Member
Joined
Dec 4, 2020
Messages
2
Office Version
  1. 2019
Platform
  1. Windows
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?
 

Some videos you may like

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
Joined
Dec 4, 2020
Messages
2
Office Version
  1. 2019
Platform
  1. Windows
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,492
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top