Help - Make "Pre-loaded" Formulas on Spreadsheet Invisible on Line Chart
Posted by Tom L. on February 14, 2002 8:31 AM
I use Excel 97. On the spreadsheet, I use columns A thru AM and rows 1 through 110. In the first five columns I enter "the date, Open, High, Low, and Close" data, and across the remaining columns have various math formulas that all start with: IF(E2="","", (or whatever the cell row - could be anything up to E110) and then enter the various formulas. I preload each column's formula down to the 110th row, and the spreadsheet stays "clean" (empty to the eye)until I enter that day's "Open, High, Low, Close" data. In other words, I drag all my formulas down from F2 to AM 110, and as I said, the spreadsheet stays "clean" until I enter the next day's data, and at that point, as I enter the data in the first five columns for that row, all the calculations across that row are just automatically accomplished - it is easy for me to administer on a daily basis.
However --- even though I may only have entered data down through row 30 on the speadsheet, for instance, the line chart picks up the fact that there are "invisible" formulas all the way down to row 110 and from the 30th "tick" on the chart, places a line at the "zero" line on "tick 31" and runs it across the "x" axis to the 110th tick. The chart is set up on the "y" axis as "zero" to "100" by tens, and the "x" axis simply goes out those 110 ticks (for the 110 days of data).
I need something that will make those preloaded, unused formulas on the spreadsheet "invisible" to the chart until a calculation is done on the spreadsheet for that particular row, and I would prefer to also keep that unused portion of the spreadsheet "clean" to the eye until I am using each row.
I have five or six lines on each chart (using data from five or six columns of the spreadsheet, obviously), and as the lines drop to "zero" after that 30th day, the chart is very confusing, so I don't want that to happen.