Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Jul 2004
    Location
    Sumner, Washington, USA
    Posts
    112
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Auto-Numbering (Excel 2000, SP-2)

    I'm still working on that same worksheet, this time trying to automatically enter charge numbers with some type of formula. It's another one of those tasks that seems straightforward, easy enough to explain, but that I don't have enough experience with Excel to figure out on my own. I've attached the sheet in question.

    Columns 1-4 contain numbers which will be entered by the user. They may follow any format, may include letters instead of numbers, or a combination. Pretty much anything goes in the first four columns. What is consistent, and for which I need the formula, are the numbers in columns 7 and 8. The relationship between the columns is always the same:
    <UL><LI>If there is a number or letter in Column 1, columns 7 and 8 are always blank.
    <LI>If there is NOT a number or letter in Column 1, column 7 is always 01 UNTIL the first time there is a new number in Column 1. Then column 7 switches to 02 in the first row following the new item in Column 1.
    <LI>Column 2 is the basis of the numbering in column 8. If there is a number or letter in column 2, row 8 starts at 01, changing to 02, 03, etc., every time the value in column 2 changes.[/list]The attached sheet has the correct numbers entered manually. As always, any help is greatly appreciated. (BTW, I really haven't just been working on this one sheet for all this time!)

    --Karyl

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Auto-Numbering (Excel 2000, SP-2)

    In R8K7, enter this formula:

    <code>=IF(ISBLANK(RC1),COUNTA(R8C1:RC1),0)</code>

    In R8K8, enter this formula:

    <code>=IF(ISBLANK(RC1),IF(ISBLANK(RC2),R[-1]C8,R[-1]C8+1),0)</code>

    Select R8K7:R8K8, copy, then paste special / formulas to R9K7:R30K8 or as far down as needed.
    Select R8K7:R30K8 or as far down as needed, and set the number format to <code>00;;</code>

    See attached modified version (zipped).

  3. #3
    2 Star Lounger
    Join Date
    Jul 2004
    Location
    Sumner, Washington, USA
    Posts
    112
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto-Numbering (Excel 2000, SP-2)

    Totally cool! It works exactly as I'd hoped, and I even understand it--although I couldn't say I'd find it any easier to do next time around. But the CountA was a new function for me, one I'm sure will come in handy. Many thanks!

    One quick question: when I first tried this, I did it in my own copy of the sheet, trying to understand the formulas, etc. And the Phase level rows that needed to be blank had zeros. I tried adding an additional level of IF to make the cell blank if it was equal to zero, but that only worked for column 7, since column 8 was adding. So I eventually set up conditional formatting to change the text to light yellow (the same color as the fill in those rows) if the cell=0. Then I took a look at yours to see how you'd done it. (If I'd done this AFTER coffee instead of before, I might not have missed that part in your message in the first place--but experimenting on my own is never a waste of time if I figure something out, even if it wasn't the best solution.)

    Anyway, more "Wow, cool!" Such an easy thing that I was making way too hard! But I couldn't find any documentation on what the custom format you entered, the 00;; represents. The "00" is easy, but what about the semicolons? Obviously, somewhere in there is the "blank if zero" part, but how does it translate? Many thanks!

    --Karyl

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Auto-Numbering (Excel 2000, SP-2)

    A custom number format can consist one to four sections, separated by semicolons. The first is for positive numbers, the second for negative numbers, the third for zero values, and the fourth for text values. In the cells with the formulas, I set the number format to <code>0;;</code>. There are three sections, but the second and third section are empty, meaning that negative and zero values will not be displayed. If you look at the built-in number formats (select Format | Cells..., Number tab, Custom category), you will see that there are several formats that consist of more than one section.

  5. #5
    2 Star Lounger
    Join Date
    Jul 2004
    Location
    Sumner, Washington, USA
    Posts
    112
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto-Numbering (Excel 2000, SP-2)

    Thanks for your very clear explanation--all of which, of course, was right at the top of the Help page I searched through, looking in vain for references to using a semicolon as a formatting code rather than looking for it where it should have been obvious: in the explanation of the four parameters available for setting a custom format. Do you ever get tired of explaining the obvious? And I can't even claim this time that Word does it differently (although it only allows for three options). This is going to be another very helpful tool that I didn't even know was available!

    I'm going to be working in Excel pretty much full-time for the next several weeks, doing entirely template building with both worksheets and charts. Do you think there's a chance, by September, that some of this will actually sink in? <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15>

    Again, thanks for both your patience and your help.

    --Karyl

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Auto-Numbering (Excel 2000, SP-2)

    > Do you ever get tired of explaining the obvious?

    I like explaining the obvious. Much easier than explaining the not-so-obvious. <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>

    > Do you think (...) that some of this will actually sink in?

    My experience is that working on (and struggling with) a concrete project is the best way to learn, much more than reading a book. And with time, the Excel object model and its idiosyncrasies will become familiar.

  7. #7
    2 Star Lounger
    Join Date
    Jul 2004
    Location
    Sumner, Washington, USA
    Posts
    112
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto-Numbering (Excel 2000, SP-2)

    Thanks for the encouraging words. I've been working on this template all day, and I've reached the point where everything is working in isolation. Now I'm trying to get all the pieces to work together. For example, some of the code worked just great--until I protected one of the worksheets. Or until I tried to run the "Insert a Row" code with the "Apply Formatting" code. Oops! I'm hindered a bit by the lack of something I've come to take for granted in Word--the ability to circumvent any standard Word command (like "File/Print" or "Insert Row") and recreate it for my own purposes. Excel doesn't seem so willing to share control!

    I was frustrated for awhile that I broke the great formula you gave me this morning. It worked fine, even with my adaptation to keep the cells blank in the rows that had no data yet. But then I tried cutting rows and inserting them elsewhere. Another, "Oops!" I felt pretty proud of myself when I finally got it to work again using some OFFSET functions. (At least, I haven't been able to break it yet!)

    Well, enough rambling. Time to get back to work. These templates go live on our Intranet on Monday morning, which means I have to get them finished tomorrow. Thankfully, I just have one more procedure to write in the Word template, so I can spend the rest of the time trying to figure out how I messed up my grouping/ungrouping, protecting/unprotecting method, based on which sheet and/or cell the user had selected, something that was working fine a few hours ago and now only works about half the time!

    Again, many thanks!

    --Karyl (who rambles when she's tired)

  8. #8
    2 Star Lounger
    Join Date
    Jul 2004
    Location
    Sumner, Washington, USA
    Posts
    112
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto-Numbering (Excel 2000, SP-2)

    Remember this worksheet? Hans, you figured out a really cool formula to automatically insert timesheet charge numbers based on the count of values in other columns. Well, it turns out that I misunderstood how they needed it to be done. I thought they were telling me that no matter what the values in the first two columns, use 01, 02, etc., for the timesheet. But it turns out that they want the actual values from the initial columns, just stripped of any punctuation. So, for example, where initially, the value A.100 in the first column would generate a billing number of 01, now it needs to be A100 (no period). I'm attaching workbook with a sheet using the original formula and another showing how it needs to be. I'm hoping there is an easy way to accomplish this system as well (although I'm nervous about the removing the punctuation part). Any ideas?

    Many thanks!

    --Karyl

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Auto-Numbering (Excel 2000, SP-2)

    Try these 2 formulas instead:
    =SUBSTITUTE(IF(AND(ISBLANK(RC1),ISBLANK(R[-1]C1)),R[-1]C,IF(ISBLANK(RC1),R[-1]C1,RC1)),".","")

    =SUBSTITUTE(IF(AND(ISBLANK(RC2),ISBLANK(R[-1]C2)),R[-1]C,IF(ISBLANK(RC2),R[-1]C2,RC2)),".","")

    and follow the rest of Hans' direction to convert to values
    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •