Sign in
 
Home20132010Other VersionsLibraryForumsGallery
TechCenter >
> 
> 
Auto increment a worksheet

Answered Auto increment a worksheet

  • Monday, April 30, 2012 2:05 PM
     
     
    Sign In to Vote
    0

    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
     
      Has Code
    Sign In to Vote
    1

    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
     
     
    Sign In to Vote
    0

    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 bppowell Monday, April 30, 2012 3:19 PM
    •  
     
  • Monday, April 30, 2012 3:41 PM
     
     
    Sign In to Vote
    0

    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
     
     
    Sign In to Vote
    0

    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
     
     
    Sign In to Vote
    0

    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
     
     
    Sign In to Vote
    0

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

    Barry

     
  • Monday, April 30, 2012 9:01 PM
     
     
    Sign In to Vote
    1

    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
     
     Answered
    Sign In to Vote
    0

    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.

    Thanks for all your help 

     
  • Tuesday, May 01, 2012 9:50 AM
     
     Answered
    Sign In to Vote
    0
    Glad you were able to solve it!

    Regards, Hans Vogelaar

    • Marked As Answer by bppowell Tuesday, May 01, 2012 9:58 AM
    •