Auto Fill Tab Names

mm178

Board Regular
Joined
Dec 24, 2009
Messages
52
I have 30 Plus tabs in my work book. I am trying to auto fill Tab Names to make it easy. Auto fill to me is to drag a cell and have the formating change for the next cell.

To make it easier i have named my cells 1 - 32 I though this would make it easier for auto fill

I am trying to drag this down a column
='1'!A$1:J$1

A1:J1 is a merged cell so there will just be a transfer of the students name on the top of the page.

'1' is the tab name, but when i drag the cell down the column I would like the next cell to say
='2'!A$1:J$1
='3'!A$1:J$1
='4'!A$1:J$1
And so on.

I will need to use this auto fill in other cells also.

Right now it is not auto filling the tab name. i can get it to auto fill and change the A1:J1 if I take off the $ sign. When I take off the $ it will still not work on the Tab name.

Any Help would be great.

thanks
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Hi MM, try this formula:

=INDIRECT("'"&ROW(A1)&"'!$A$1")

When you fill down, only the ROW(A1) part will update. ROW(A1) = 1, so it will become 2, then 3, etc. referring to sheet names 1, 2, 3, etc.
 
Upvote 0
Yes it worked.
Will this work if I change the tab Name later in the process?

Thanks

So Would this work the same for Example J55 from every tab

=INDIRECT("'"&ROW(A1)&"'!$J$55")

Thanks Again
 
Last edited:
Upvote 0
A similar formula may be possible if you change the tab names, like if you changed them to Sheet1, Sheet2, Sheet3, etc. But if you change them to non-related tab names then no, doing so would prove difficult without a macro.

The example I provided should work just as well replacing $A$1 with $J$55.
 
Upvote 0
I plan to change the Sheet Name to a students first name.

I guess it might just be easier to just manually type to =Tab name.

Thanks
 
Upvote 0
I believe if you put the Student's names in a range of cells, let's say Z1:Z20, then you could use a similar formula to my first response just without the ROW() function:

=INDIRECT("'"&Z1&"'!$A$1")

This will look at cell $A$1 on the sheet with the name from cell Z1 on the activesheet.
 
Upvote 0

Forum statistics

Threads
1,213,520
Messages
6,114,099
Members
448,548
Latest member
harryls

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
Back
Top