More Help - "Preloaded Formulas on Spreadsheet Invisible on

[MNTVL]

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???????

 

[Barrie Davidson]

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())

 

[Guest]

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.

 

[Juan Pablo González]

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

 

[MNTVL]

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

 

[Juan Pablo González]

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.

 

[MNTVL]

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?