More Help - "Preloaded Formulas on Spreadsheet Invisible on

MNTVL · Feb 16th, 2002, 12:25 PM

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 · Feb 16th, 2002, 12:52 PM

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

Feb 20th, 2002, 06:02 PM

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 · Feb 20th, 2002, 06:58 PM

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

MNTVL · Feb 21st, 2002, 10:00 AM

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 · Feb 22nd, 2002, 01:44 PM

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 · Feb 22nd, 2002, 02:13 PM

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?

Feb 16th, 2002, 12:52 PM	#2
Barrie Davidson MrExcel MVP Join Date: Feb 2002 Location: Winnipeg Posts: 2,330	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

Feb 20th, 2002, 06:58 PM	#4
Juan Pablo González MrExcel MVP Join Date: Feb 2002 Location: Bogota, Colombia Posts: 11,927	You could use Conditional Formatting to hide the #N/A. __________________ Regards, Juan Pablo González http://www.juanpg.com

Feb 22nd, 2002, 01:44 PM	#6
Juan Pablo González MrExcel MVP Join Date: Feb 2002 Location: Bogota, Colombia Posts: 11,927	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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Feb 16th, 2002, 12:25 PM	#1
MNTVL New Member Join Date: Feb 2002 Location: Tom Lotzer Posts: 7	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???????

Feb 20th, 2002, 06:02 PM	#3
Guest Posts: n/a	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.

Feb 21st, 2002, 10:00 AM	#5
MNTVL New Member Join Date: Feb 2002 Location: Tom Lotzer Posts: 7	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

Feb 22nd, 2002, 02:13 PM	#7
MNTVL New Member Join Date: Feb 2002 Location: Tom Lotzer Posts: 7	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?