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

MNTVL

New Member
Joined
Feb 15, 2002
Messages
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
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,549
Messages
6,114,264
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top