Results 1 to 12 of 12
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Empty cells until typed in (Excel 2000)

    I am attaching a copy of an example worksheet. I have running sums in the Alpha, Bravo and Charlie sheets. My question is...Notice how from row 6 down (after autofilling the running sum in these sheets), I haven't typed any expenses yet, and was wondering how to keep these cells empty until I type something into them, without losing my running formula. Is this possible? I hope I'm clear. I don't want any zeros in column A (on the Alpha, Bravo, Charlie sheets) that I haven't typed an expense in the total sheet for yet. I don't want any running expense totals to show in the running sum column (on the alpha, bravo, charlie sheets) if an expense hasn't been brought over from the total sheet yet and placed in Column A.
    Thanks for the help...this is one I'm just stuck on.
    NMPadgett
    Attached Files Attached Files
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  2. #2
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Empty cells until typed in (Excel 2000)

    I guess (confused by the design) that the formula that you want is
    <pre>=IF(A2="","",SUM($A$2:A2))</pre>

    instead of
    <pre>=SUM($A$2:A2)</pre>

    then auto-fill that and clear the zeros in column A. HTH --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Empty cells until typed in (Excel 2000)

    Sammy,
    What a cool idea. It didn't seem to work though. Maybe you could enter it into the spreadsheet I sent, and then attach it so I can your results. I may be doing something screwy. It wouldn't be the first time...mean time, anyone else with ideas, shoot them to me. [img]/forums/images/smilies/smile.gif[/img]
    NMPadgett
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  4. #4
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Empty cells until typed in (Excel 2000)

    Sorry you had problems. Here's your workbook with the addition.
    Attached Files Attached Files
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  5. #5
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Ross-on-Wye, Herefordshire, United Kingdom
    Posts
    212
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Empty cells until typed in (Excel 2000)

    Highlight the cells, Ctrl+1 (or however you like to do this) to access cell formatting. Put a custom format like so:

    #,##0.00;[red ]-#,##0.00;

    No zeros will display. They key is the THIRD ";" in the format. Whatever you put there tells Excel what to do with zero amounts. If you put nothing, it doesn't display anything in a cell of the value is 0.

    N.B. Do NOT leave a space after [Red I have to get round the lounge formatting.

    Hope that helps.

    Regards
    Peter

  6. #6
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Empty cells until typed in (Excel 2000)

    This works beautifully on the data in Column A on the Alpha, Bravo and Charlie sheets. However, what about Column B where my running total is. Can I get rid of the data in it without losing my formulas using a custom format?
    PS - Sammy...it is not exactly what I am after, however, the concept is "excellent". It rids my formula every time. Even in your worksheet you sent back. Maybe its me, I dunno.
    NMPadgett
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  7. #7
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Empty cells until typed in (Excel 2000)

    Another option is to use Tools, Options and in the Windows options of the View Tab uncheck Zero values. It will only apply to the active sheet.

    Andrew C

  8. #8
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Empty cells until typed in (Excel 2000)

    Thats the easiest of all Andrew. Except what about Column B on the Alpha Bravo and Charlie sheets. How do I make that column empty without removing the formulas. Only filling in the emptiness after I type a deduction in the Total sheet for one of the battalions (which of course shows up in Column A on one of the battalion sheets).
    NMPadgett
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  9. #9
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Ross-on-Wye, Herefordshire, United Kingdom
    Posts
    212
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Empty cells until typed in (Excel 2000)

    Yes you can.. Custom formats just change how Excel displays the data but does not affect the actual data in the cell itself. e.g.

    A1= 1234.56789

    if you format the cell #,##0.00;[Red ]-#,##0.00;

    What you will see displayed is 1,234.57 (Note the rounding)

    if in A2 you put =TEXT(A1,"#,##0.000000000")

    What you will see is something like:

    1234.567890000 (However many zeros i added!)

    The point being, it doesn't affect the real value, just how it's displayed.

    N.B. You can force Excel to calculate at the 'Precision displayed' - Tool, Options, Calculation, Precision as displayed check box.

    Regards
    Peter

  10. #10
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Empty cells until typed in (Excel 2000)

    Sorry, had not looked at you workbook and assumed you just wanted to hide zeros. Set the View zeros to off and in B2 of the sheets concerned use =IF(A2,SUM($A$2:A2),0) and copy down. I attach an example in your workbook.

    Andrew
    Attached Files Attached Files

  11. #11
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Empty cells until typed in (Excel 2000)

    Thank you Andrew...I should have figured that one out myself. Have so many topics going today, but of course, you can relate to that...you have question after question here to help folks with. I know your mind gets pulled in many directions in a given day too. You guys are all so great. Thank you as always, thank you, thank you, thank you...I salute you...you all do so much.
    NMPadgett
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  12. #12
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Ross-on-Wye, Herefordshire, United Kingdom
    Posts
    212
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Empty cells until typed in (Excel 2000)

    Ooh! I gorfot about that!

Posting Permissions

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