Automatically increment worksheet numbers

**sdaddy** · 06-09-2009, 01:29 AM

Hi All,

In my workbook I have a summary sheet and then will have approx 200 tabs (worksheets) after the summary sheet. Of those 200 tabs each tab is exactly the same and i need to pull the exact same data from each and every tab into the summary sheet.

Can someone please advise me on how to automatically increase the tab number by one?

e.g. My tab numbers will be 01 through to 200. I am currently using as a formula: ='01'!$L$6

Obviously pointing to Tab 01 and in that tab Cell L6..

i need this to automatically change when i drag the cell down to:
='01'!$L$6
='02'!$L$6
='03'!$L$6
='04'!$L$6
..
='200'!$L$6 ... and so on.

Thanks in advance all, much appreciated.

**Excel Matic** · 06-09-2009, 02:49 AM

Here is the formula:

Please Login or Register  to view this content.

Just take care of the following couple of things before you use the code;

Replace H17 and $H$17 with the cell address where you want to copy this function and do not replace anything else. So, for example the first cell in which you want to put this function is A1, then H17 should be replaced with A1 and $H$17 should be replaced with A1 and $A$1, respectively. Then simply drag down the formula and it will be done
This code will work for till sheet numbers less than 10,000.

In case you face any issues using this, just PM with the link to this thread.

**DonkeyOte** · 06-09-2009, 02:53 AM

I would propose:

="'"&TEXT(ROWS(A$1:A1),"00")&"'!L6"
copy down

To execute the above you have 2 options

1: adjust the above text string to incorporate an "=" and copy/paste values the results over the original formulae then run Edit -> Replace, Replacing = with = (coerces the strings to formulae)

2: use INDIRECT

=INDIRECT("'"&TEXT(ROWS(A$1:A1),"00")&"'!L6")
copy down

NOTE: INDIRECT is Volatile so should be avoided if used in large quantities

**Excel Matic** · 06-09-2009, 02:59 AM

Hi DonkeyOte,

Your first option is really great!!

**DonkeyOte** · 06-09-2009, 02:59 AM

Originally Posted by Excel Matic

In case you face any issues using this, just PM with the link to this thread.

All questions pertaining to a thread should be made within the thread, not privately.

sdaddy, if you have questions please ensure you post them here directly.

Thanks

**Excel Matic** · 06-09-2009, 03:03 AM

DonkeyOte -

I never asked anyone to ask me questions through PM. I only asked him to send me the link to this thread to me so that I can be reminded of this thread.

I have read all the rules and I respect them.

Hope it is clear now!

**sdaddy** · 06-09-2009, 03:09 AM

Thanks for your help DonkeyOte. I have stuck with the Indirect function, i understand the volatility and hopefully i dont end up having to use too large quantities of worksheets.

One further question i have is that after using...

=INDIRECT("'"&TEXT(ROWS(A$1:A1),"00")&"'!H7")

Note: i have changed it for cell H7. H7 is a field which checks two cells E21 and E22 in sheet '01' and outputs the field that displays text in it...

=IF(ISBLANK('01'!E21),IF(ISBLANK('01'!E22),"",'01'!E22),'01'!E21)

how would i then be able to incorporate the indirect formula to populate my if(isblank) statements? i have a couple of them...

i tried using...

=INDIRECT("'"&TEXT(ROWS(A$1:A1),"00")&IF(ISBLANK('01'!E21),IF(ISBLANK('01'!E22),"",'01'!E22),'01'!E21)))

with no luck...

Thanks again.

**DonkeyOte** · 06-09-2009, 03:14 AM

I offered a slight variation to your other thread:

http://www.excelforum.com/excel-gene...one-value.html

If the values present in the cells are you could go for:

Please Login or Register  to view this content.

**royUK** · 06-09-2009, 09:55 AM

Excel matic - code tags are for posting code, not formulae!

**DonkeyOte** · 06-09-2009, 11:45 AM

hi Roy, I believe the issue of spurious spaces still persists post server move if you try to post long formulae less code tags... (see sdaddy's last formulae in their last post)

**sdaddy** · 06-09-2009, 07:22 PM

Hi Donkey Ote,
I tried using ure given formula...

=TRIM(REPT(INDIRECT("'"&TEXT(ROWS(A$1:A1),"00")&"'!E21")&" "&INDIRECT("'"&TEXT(ROWS(A$1:A1),"00")&"'!E22"),1))

However, if both text fields (E21 and E22) are filled in the cell displays both values. How can i cut out redundant data and stop any repetition for occuring?

Help much appreciated.

**tigertiger** · 06-09-2009, 08:03 PM

try to the following formula:

PHP Code:


=IF(ISBLANK(INDIRECT("'"&TEXT(ROWS(A$1:A1),"00")&"'!E21")),IF(ISBLANK(INDIRECT("'"&TEXT(ROWS(A$1:A1),"00")&"'!E22")),"",INDIRECT("'"&TEXT(ROWS(A$1:A1),"00")&"'!E22")),INDIRECT("'"&TEXT(ROWS(A$1:A1),"00")&"'!E21"))

**tigertiger** · 06-09-2009, 08:12 PM

Because there is no space in the name sheets created in TEXT(ROWS(A$1:A1),"00"), we should use the shorter formula:

PHP Code:


=IF(ISBLANK(INDIRECT(TEXT(ROWS(A$1:A1),"00")&"!E21")),IF(ISBLANK(INDIRECT(TEXT(ROWS(A$1:A1),"00")&"!E22")),"",INDIRECT(TEXT(ROWS(A$1:A1),"00")&"!E22")),INDIRECT(TEXT(ROWS(A$1:A1),"00")&"!E21"))

Do you think so?

OR, with the next formula (may include SPACE):

PHP Code:


=IF(ISBLANK(INDIRECT(TEXT(ROWS(A$1:A1),"'00")&"'!E21")),IF(ISBLANK(INDIRECT(TEXT(ROWS(A$1:A1),"'00")&"'!E22")),"",INDIRECT(TEXT(ROWS(A$1:A1),"'00")&"'!E22")),INDIRECT(TEXT(ROWS(A$1:A1),"'00")&"'!E21"))

.

**sdaddy** · 06-09-2009, 08:20 PM

Thanks everyone for the help. Much appreciated. This workbook issue is now complete. Tigertiger i used your shorter formula and agreed it works correctly.

**tigertiger** · 06-09-2009, 08:30 PM

OR another option

PHP Code:


=IF(ISBLANK(INDIRECT(TEXT(ROWS($1:1),"'00")&"'!E21")),IF(ISBLANK(INDIRECT(TEXT(ROWS($1:1),"'00")&"'!E22")),"",INDIRECT(TEXT(ROWS($1:1),"'00")&"'!E22")),INDIRECT(TEXT(ROWS($1:1),"'00")&"'!E21"))

now,
+ shorter
+ and you can delete column A that doesn't effect the result
.

**DARK_STALKER** · 11-29-2012, 11:17 AM

Hi

I have found this thread to be a potential solution to a similar problem only I cant seem to get it to work correctly.

Could someone please point me in the wright direction.

I have data in worksheets that make up the 52 weeks of the year. Each sheet is numbered like this "WK1" "WK2" and so on... up to "WK52"

Each sheet is setup exactly the same so all cells are the same.

So I have a summary sheet that structures the data from all the week sheets to plot to graph all within the same file.

Thing is when I link the first cell to the first sheet say WK1 and then try to drag the formula down to the worksheet number does not increment

Tried the code in the beginning of this post and it displays the worksheet and cell number in the cell cant get the actual data to be displayed.

When I drag this formula down I want it to index the worksheet number

=WK44!$AG$14

Like this

=WK44!$AG$14
=WK45!$AG$14
=WK46!$AG$14

This is what I get in the cell when I use the formula given in the post

WK01! $AG$14

Any help on this would be greatly appreciated.

**martindwilson** · 11-29-2012, 11:20 AM

Welcome to the Forum, unfortunately:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.

**gigilbert** · 11-18-2015, 05:59 AM

Referencing C20.png

I am having trouble referencing C20. Any comments on this?

Thanks in advance.

**Glenn Kennedy** · 11-18-2015, 06:02 AM

Hi. welcome to the forum.

Please don't open an old thread. It will most likely be ignored. Start a new one.

Explain more clearly what your problem is. Also, since you have attached a picture, we really can't tell what you are trying to do. So... please attach a sample workbook. Make sure there is enough data to demonstrate your need. Make sure your desired results are shown, mock them up manually if necessary. Remember to remove ALL confidential information first!!!

Click on GO ADVANCED and use the paperclip icon to open the upload window.

The paperclip icon

Automatically increment worksheet numbers

LinkBack

Thread Tools

Rate This Thread

Display

Automatically increment worksheet numbers

Re: Automatically increment worksheet numbers

Re: Automatically increment worksheet numbers

Re: Automatically increment worksheet numbers

Re: Automatically increment worksheet numbers

Re: Automatically increment worksheet numbers

Re: Automatically increment worksheet numbers

Re: Automatically increment worksheet numbers

Re: Automatically increment worksheet numbers

Re: Automatically increment worksheet numbers

Re: Automatically increment worksheet numbers

Re: Automatically increment worksheet numbers

Re: Automatically increment worksheet numbers

Re: Automatically increment worksheet numbers

Re: Automatically increment worksheet numbers

Re: Automatically increment worksheet numbers

Re: Automatically increment worksheet numbers

Re: Automatically increment worksheet numbers

Re: Automatically increment worksheet numbers

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions