Auto increment a worksheet

# Auto increment a worksheet

• Monday, April 30, 2012 2:05 PM

I have the following formula, when I autofill down I want sheet 1a to change to 2a then 3a ,

the formula is,

=SMALL('1a'!\$P:\$P,COUNTIF('1a'!\$P:\$P,"< 10")+1)

I have tried INDIRECT but it does not seem to work for me

any ideas

Barry

### All Replies

• Monday, April 30, 2012 2:26 PM

If the first formula is in row 1:

`=SMALL(INDIRECT("'"&ROW()&"a'!\$P:\$P"), COUNTIF(INDIRECT("'"&ROW()&"a'!\$P:\$P"),"< 10")+1)`

Regards, Hans Vogelaar

• Monday, April 30, 2012 3:13 PM

Thank you for that, it seems to have a problem in that is works for the first 19 values but then gives and REF error.

I have 53 sheets in total

Also it does not appear to pick up the correct small value

Barry

• Edited by Monday, April 30, 2012 3:19 PM
•
• Monday, April 30, 2012 3:41 PM

The formula should work for any number of sheets, as long as they are named 1a, 2a, ..., 20a, 21a, ...

It performs the exact equivalent of your original formula: return the lowest value >=10 in column P. If that's not what you want, please explain what you want the formula to do.

Regards, Hans Vogelaar

• Monday, April 30, 2012 3:48 PM

The worksheets are named 1a, 1b, 1c then 2a, 2b, 2c, then 3a, 3b, 3c  ........ 18a, 18b, 18c

Sorry for not making it clear

Also the value returned in the first cell without the Indirect part of the formula is, 10.01618

When I put in the formula given with the Indirect function it returns, 10.803 there is no such number in column P on sheet 1a.

Barry

• Monday, April 30, 2012 4:25 PM

You can create separate formulas for the series 1a, 2a, ... and 1b, 2b, ... and 1c, 2c, ...

Without seeing the data I have no idea why the formula returns a different result than you expect.

Regards, Hans Vogelaar

• Monday, April 30, 2012 8:08 PM

I really apreciate the help, could I send you the file?

Barry

• Monday, April 30, 2012 9:01 PM

You could make a stripped-down copy of the workbook (without sensitive information) available through one of the websites that let you upload and share a file, such as Windows Live SkyDrive (https://skydrive.live.com) or DropBox (http://www.dropbox.com), then post a link here.

Or register at www.eileenslounge.com (it's free) and post a message in the Excel forum. You can attach files up to 250 KB to a post there.

Regards, Hans Vogelaar

• Tuesday, May 01, 2012 9:16 AM

I did not know that if you put the formula in a row other than row 1 you had to apply an adjustment.

Applying the adjustment and then renaming the worksheets to 1a 2a 3a ....... 53a

the formula works.

I think I will advise that they follow this, it will be quicker than trying to adjust the formula.