More Help - "Preloaded Formulas on Spreadsheet Invisible on
More Help - "Preloaded Formulas on Spreadsheet Invisible on
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: More Help - "Preloaded Formulas on Spreadsheet Invisible on

  1. #1
    New Member
    Join Date
    Feb 2002
    Location
    Tom Lotzer
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    I want to pre-load all my formulas across and down the spreadsheet as I start to note and line-chart 5 months of data on a daily basis.

    One of my formulas across the columns is:
    IF(E2="","",SUM(B2:D2))
    What I am trying to do by this is say, "if there is nothing in cell E2, don't compute and don't show anything on that "tick" of the chart (and also, don't show anything in the E2 cell on the spreadsheet), but if there is something in Cell E2 on the spreadsheet, then compute the SUM(B2:D2) formula, and then show the results of that computation on the appropriate "tick" of the line chart.

    One attempted solve is:
    IF(E2="",NA(),SUM(A2:D2)) - however, this puts a #N/A or somesuch in the empty cell (E2) on the spreadsheet (remember, the formulas are all pre-loaded) - I want the spreadsheet "clean" until the pre-loaded formula must compute some data that is placed on that row (my input data goes from columns A through D, my formulas go across the spreadsheet from E to AM and down to row 110, so my spreadsheet and charts can get quite confusing with too much clutter.

    Can anyone help???????

  2. #2
    MrExcel MVP Barrie Davidson's Avatar
    Join Date
    Feb 2002
    Location
    Winnipeg
    Posts
    2,330
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    How about adding another column in your worksheet (to the right of your used section), put in your second formula [IF(E2="",NA(),SUM(A2:D2))] in that column, and then chart off that column?

    Sorry to be the bearer of bad news but you won't be able to have a clean spreadsheet (i.e., returning "") and a chart that graphs nothing (i.e., NA())
    Barrie Davidson

    "You're only given a little spark of madness. You mustn't lose it." - Robin Williams

  3. #3
    Guest

    Default

    Thanks for your answer - I've been out of town for several days, and that's why I haven't responded.

    I appreciate your input - but don't like your message. I guess you are correct. I just can't believe that Microsoft wouldn't have had the occasion or inclination to have both a clean spreadsheet and chart until the calculations have been made (while at the same time pre-loading the formulas) after all their revisions to Excel. It would make both look more professional.

    Thanks again for your input.

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    You could use Conditional Formatting to hide the #N/A.
    Regards,

    Juan Pablo González
    http://www.juanpg.com

  5. #5
    New Member
    Join Date
    Feb 2002
    Location
    Tom Lotzer
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    How does the "conditional formatting" work - I haven't heard of that before (that's not hard to believe, since I'm not that good technically).

    Please advise - thanks.

    MNTVL
    tlotzer@harmoninc.com

  6. #6
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Select the cells you want to format. (I'm assuming it's A1:A10, so select A1:A10). Go to Format, Conditional Formatting.

    Change the "Value is" to "Formula Is" and put this formula

    =ERROR.TYPE(A1)=7

    And click the Pattern button. On the Font Tab, select the font color to white (Or the background color of your cells). Click Ok, and Ok.
    Regards,

    Juan Pablo González
    http://www.juanpg.com

  7. #7
    New Member
    Join Date
    Feb 2002
    Location
    Tom Lotzer
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Fantastic - I'll give it a try. Thank you for your input. This will make a big difference in my outlook to doing this spreadsheet.

    By the way, if you know how to get rid of that "horrible" boarder line that goes around a chart with the boxed corners, and boxes in the middle of each of the sides, please let me know.

    I am a big fan of Excel v2.1 (yes I know how outdated that makes me, but sometimes, simple is better). I use nine different "template" charts per spreadsheet, and I also include on the template, a lot of arrows that I can just pick and use if I have a need over the course of any of the 110 days (i.e., the 110 rows I use my formulas on). It seems as I simply "touch" any of the charts using Excel 97, that horrible boxed border always shows up. Is there a way to hide it forever after you have the chart set up the way you want it?

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com