Help!Make "Pre-loaded" Formulas on Spreadsheet Invisible on

MNTVL · Feb 16th, 2002, 05:54 PM

I place "Open, High, Low, Close" data on a daily basis (110 rows in all) and use formulas going from column F to AM.
A sample formula would be:
IF(E13="","",SUM(N7:N13)). Column E is where I place the closing price.
What I want - If there is nothing in cell E13 of the spreadsheet, don't compute and don't show anything on the row 13 "tick" of the line chart (nor show anything in cell N13 of the spreadsheet - where the formula is pre-loaded), but if there is something in cell E13, then compute the SUM(N7:N13) pre-loaded formula, and then show the results of that computation in cell N13 of the spreadsheet, as well as the results of that computation on the applicable row 13 "tick" of the line chart.
I want to keep the spreadsheet "clean" until I have placed "Open, High, Low, Close" data on each row and then have the formulas along each specific row "automatically" do all their calculations and fill in the answers, both on the spreadsheet and on the various charts linked to that spreadsheet.
tlotzer@harmoninc.com

Derek · Feb 17th, 2002, 01:23 AM

Hi
If I understand your question correctly, try

Say A5:A10 is for OPEN data (6 CELLS)
Say B5:B10 is for HIGH data
Say C5:C10 is for LOW data
Say D5:D10 is for CLOSE data
If E1 totals a Counta formula on each of the above it should give 4*6=24 if all those cells have data.
You could then use an if statement at the beginning of all your other formulas returning "" unless E1 = 24

HTH
Derek

MNTVL · Feb 17th, 2002, 12:01 PM

Derek: Thanks for replying. No, the current calendar dates go in column A and then columns B through E have the open, high, low, and close that I put in for that day's stock quote. The reason for asking if the E column has anything in it, is simply to say, if there isn't anything in column E on that row, don't put anything in the other cells across the row. Columns F through AM have formulas that are based from the Open, High, Low, Close data I put there each day.

Sorry, I'm a little confusing the way I explain things. I am not very strong technically (that's why I want the spreadsheet and chart as simple as possible)

_________________

[ This Message was edited by: MNTVL on 2002-02-17 11:01 ]

Derek · Feb 17th, 2002, 08:46 PM

MNTVL
Okay, If you have 110 rows in column E (say E10:E120) which must all contain data before your formulas kick in, put this formula in E1 =COUNTA(E10:E120). When every cell in that range contains data E1 will display 110.
So prefix all your other formulas so that if E1 is not equal to 110 they return "" (eg =If(E1<>110,"", plus the rest of your formula).
Then your spreadsheet will remain clean until you make your final entry in E120
Derek

Derek

Derek · Feb 17th, 2002, 08:57 PM

Maybe I misunderstood you.
If you want your formulas to display row by row but only after you have put data in the E column of that row, just prefix every formula on that row eg
If(E1 = "","", (for row 1)
If(E2 = "","", (for row 2)
If(E3 = "","", (for row 3)
Derek

MNTVL · Feb 18th, 2002, 06:33 AM

Derek, with your latest response you are on the right track with the
IF(E2="","",....
but then the chart picks this up and puts a "zero" on that day's "tick" on the chart, so if I have entered data in say, 30 rows and I now have a chart with 30 "ticks", and since there is no data in E31 and beyond to the row 110, the chart maps that out as a "zero" from row 31 to row 110, and when you have five or six different graph lines on the chart, this gets very confusing with everything all of a sudden going to "zero" - so I don't want the chart to be able to "see" the pre-loaded formula on row 31 on the spreadsheet until I have loaded data on that row.
The best I can come up with so far is to start all the formulas with:
IF(E2="",NA(),
which evidently tells the chart that this is "text" and not "formula", but then that leaves the spreadsheet showing "#NA" all the way down that specific column to row 110 - it doesn't show up on the chart, but the #NA sure does on the spreadsheet, and I would just like the spreadsheet "clean" of anything in those pre-loaded but unused cells.
I'm beginning to wonder if there is a "fix" for this issue, although you would think it should have been solved by Excel(I had the same problem using the very old Excel v2.1 - which I just gave up last summer as my very old PC died).
Sure hope you can help, but I'm not sure anyone can.

Derek · Feb 19th, 2002, 01:07 AM

Aha
Well, this is a long shot, but how about having dummy columns for your data input with the real columns feeding your formulas hidden. Use a worksheet macro that automatically copies and pastes from the dummy columns to your hidden columns when you have made an entry in the last cell. Include a macro button to clear all entries (visible and hidden). At the beginning of your macro type Application.screenupdating = false (to minimise flicker).
Also, if possible, custom format your cells so that zero is not displayed eg #;-#;

Derek

[ This Message was edited by: Derek on 2002-02-19 00:21 ]

MNTVL · Feb 21st, 2002, 09:51 AM

Hi Derek: I've been out of town all week, so just got into my e-mails this morning here at work.
Your "fix" sounds interesting. I'll give it a try tonight after work (this is a non-work related project).

Thanks for the feedback. Sounds as if you have a solid technical background - unlike me.

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

Feb 16th, 2002, 05:54 PM	#1
MNTVL New Member Join Date: Feb 2002 Location: Tom Lotzer Posts: 7	I place "Open, High, Low, Close" data on a daily basis (110 rows in all) and use formulas going from column F to AM. A sample formula would be: IF(E13="","",SUM(N7:N13)). Column E is where I place the closing price. What I want - If there is nothing in cell E13 of the spreadsheet, don't compute and don't show anything on the row 13 "tick" of the line chart (nor show anything in cell N13 of the spreadsheet - where the formula is pre-loaded), but if there is something in cell E13, then compute the SUM(N7:N13) pre-loaded formula, and then show the results of that computation in cell N13 of the spreadsheet, as well as the results of that computation on the applicable row 13 "tick" of the line chart. I want to keep the spreadsheet "clean" until I have placed "Open, High, Low, Close" data on each row and then have the formulas along each specific row "automatically" do all their calculations and fill in the answers, both on the spreadsheet and on the various charts linked to that spreadsheet. tlotzer@harmoninc.com

Feb 17th, 2002, 01:23 AM	#2
Derek Board Regular Join Date: Feb 2002 Location: Perth Australia Posts: 1,567	Hi If I understand your question correctly, try Say A5:A10 is for OPEN data (6 CELLS) Say B5:B10 is for HIGH data Say C5:C10 is for LOW data Say D5:D10 is for CLOSE data If E1 totals a Counta formula on each of the above it should give 4*6=24 if all those cells have data. You could then use an if statement at the beginning of all your other formulas returning "" unless E1 = 24 HTH Derek

Feb 17th, 2002, 12:01 PM	#3
MNTVL New Member Join Date: Feb 2002 Location: Tom Lotzer Posts: 7	Derek: Thanks for replying. No, the current calendar dates go in column A and then columns B through E have the open, high, low, and close that I put in for that day's stock quote. The reason for asking if the E column has anything in it, is simply to say, if there isn't anything in column E on that row, don't put anything in the other cells across the row. Columns F through AM have formulas that are based from the Open, High, Low, Close data I put there each day. Sorry, I'm a little confusing the way I explain things. I am not very strong technically (that's why I want the spreadsheet and chart as simple as possible) _________________ [ This Message was edited by: MNTVL on 2002-02-17 11:01 ]

Feb 17th, 2002, 08:46 PM	#4
Derek Board Regular Join Date: Feb 2002 Location: Perth Australia Posts: 1,567	MNTVL Okay, If you have 110 rows in column E (say E10:E120) which must all contain data before your formulas kick in, put this formula in E1 =COUNTA(E10:E120). When every cell in that range contains data E1 will display 110. So prefix all your other formulas so that if E1 is not equal to 110 they return "" (eg =If(E1<>110,"", plus the rest of your formula). Then your spreadsheet will remain clean until you make your final entry in E120 Derek Derek

Feb 17th, 2002, 08:57 PM	#5
Derek Board Regular Join Date: Feb 2002 Location: Perth Australia Posts: 1,567	Maybe I misunderstood you. If you want your formulas to display row by row but only after you have put data in the E column of that row, just prefix every formula on that row eg If(E1 = "","", (for row 1) If(E2 = "","", (for row 2) If(E3 = "","", (for row 3) Derek

Feb 18th, 2002, 06:33 AM	#6
MNTVL New Member Join Date: Feb 2002 Location: Tom Lotzer Posts: 7	Derek, with your latest response you are on the right track with the IF(E2="","",.... but then the chart picks this up and puts a "zero" on that day's "tick" on the chart, so if I have entered data in say, 30 rows and I now have a chart with 30 "ticks", and since there is no data in E31 and beyond to the row 110, the chart maps that out as a "zero" from row 31 to row 110, and when you have five or six different graph lines on the chart, this gets very confusing with everything all of a sudden going to "zero" - so I don't want the chart to be able to "see" the pre-loaded formula on row 31 on the spreadsheet until I have loaded data on that row. The best I can come up with so far is to start all the formulas with: IF(E2="",NA(), which evidently tells the chart that this is "text" and not "formula", but then that leaves the spreadsheet showing "#NA" all the way down that specific column to row 110 - it doesn't show up on the chart, but the #NA sure does on the spreadsheet, and I would just like the spreadsheet "clean" of anything in those pre-loaded but unused cells. I'm beginning to wonder if there is a "fix" for this issue, although you would think it should have been solved by Excel(I had the same problem using the very old Excel v2.1 - which I just gave up last summer as my very old PC died). Sure hope you can help, but I'm not sure anyone can.

Feb 19th, 2002, 01:07 AM	#7
Derek Board Regular Join Date: Feb 2002 Location: Perth Australia Posts: 1,567	Aha Well, this is a long shot, but how about having dummy columns for your data input with the real columns feeding your formulas hidden. Use a worksheet macro that automatically copies and pastes from the dummy columns to your hidden columns when you have made an entry in the last cell. Include a macro button to clear all entries (visible and hidden). At the beginning of your macro type Application.screenupdating = false (to minimise flicker). Also, if possible, custom format your cells so that zero is not displayed eg #;-#; Derek [ This Message was edited by: Derek on 2002-02-19 00:21 ]

Feb 21st, 2002, 09:51 AM	#8
MNTVL New Member Join Date: Feb 2002 Location: Tom Lotzer Posts: 7	Hi Derek: I've been out of town all week, so just got into my e-mails this morning here at work. Your "fix" sounds interesting. I'll give it a try tonight after work (this is a non-work related project). Thanks for the feedback. Sounds as if you have a solid technical background - unlike me.