Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

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

  1. #1
    New Member
    Join Date
    Feb 2002
    Location
    Tom Lotzer
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Perth Australia
    Posts
    1,584
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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



  3. #3
    New Member
    Join Date
    Feb 2002
    Location
    Tom Lotzer
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Perth Australia
    Posts
    1,584
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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



  5. #5
    Board Regular
    Join Date
    Feb 2002
    Location
    Perth Australia
    Posts
    1,584
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  6. #6
    New Member
    Join Date
    Feb 2002
    Location
    Tom Lotzer
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  7. #7
    Board Regular
    Join Date
    Feb 2002
    Location
    Perth Australia
    Posts
    1,584
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  8. #8
    New Member
    Join Date
    Feb 2002
    Location
    Tom Lotzer
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •