Results 1 to 4 of 4
  1. #1
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Update cell only on condition (Excel 97/2000)

    A quicky.... if I don't want to use VBA-code, how would I create a structure that would allow me to display a value in a cell if the sheet is loaded between e.g. this monday and this tuesday.
    Obviously a simple IF-function would do that.... problem is that when the sheet is opened later, the cell should keep the value it received earlier.
    Are there ways to prevent circular refs here (note that calculations should remain automatic)

    Thanks,

    EJ

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update cell only on condition (Excel 97/2000)

    Can you give a little better explaination of exactly what you mean? By loaded, do you mean that it is opened and displayed on the screen? By "between e.g. this monday and this tuesday" do you mean between July 2nd and 3rd, or do you mean Monday or Tuesday of the current week? What do you mean by "when the sheet is opened later, it should keep the value it received earlier?" Do you mean that if it was opened between the Monday and Tuesday it should always display a value, but if it wasn't then no value should be displayed if it is opened later? Your wording is open to many different interpretations.
    Legare Coleman

  3. #3
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update cell only on condition (Excel 97/2000)

    Sorry for my bad formulation of the problem. Let me try to be a little more clear and answer your questions.

    The basic idea is that in a sheet, a sort of progress table is being created which indicates e.g. week-by-week what the values of certain cells were. So what we need is that e.g. on mondays and tuesdays (or in any other time-window), the current value of some cells are copied to another location were a table is maintained. A week later, the same happens in the next row of the table etc. After a few weeks, the table e.g. could look like:

    Date Value1 Value2
    July 01 1 3
    July 08 2 5
    July 15 4 9
    July 22 5 12

    As you can see, the entries are sort of "snapshots" of how the data was on mondays and/or tuesdays.
    The reason for the time-window is because this can obviously only work when the sheet is loaded (open/displayed) and it could happen sometimes that people do not do that each Monday but sometimes only on Tuesday.
    Again, if we don't want to use VBA (else it would be quite simple), of course getting the 'snapshot' displayed is not too difficult as well. The "value1"-cells should have formulas which would be something like:

    IF {Date in cell to the left}={DateNow} OR
    {Date in cell to the left+1} = {DateNow} THEN
    {Thiscellvalue=OtherSheet!Targetcell}

    So, if the current date is either the date for this row (e.g. July 01 in the first row above) or one day later (ergo: Monday or Tuesday if July 01 was a Monday) then link to the current-value in an other sheet.

    Now here's the problem (maybe it's so simple that I don't see it myself): I want the values to STICK if time goes by. So next week, the second row should get its values but the previous row should not change anymore. I don't see how to do that as the TargetCell has changed of course...
    So the "ELSE" statement that seems to be missing above is what is giving me problems, if should say something like:

    ELSE {Keep the value as it is now}

    As the LINK has clearly changed, the 'value' is no longer available as "OtherSheet!TargetCell"

    Hope this is more clear.

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update cell only on condition (Excel 97/2000)

    Hi ErikJan,

    The only way to do this is to use deliberate circular references, in which the Else clause you mentioned refers to the cell itself.

    You would then have to set iteration to true (Tools, Options, calculation) for this to work.

    BUT (why is there always a but <g>). If this workbook is NOT loaded as the first workbook, iteration will be set at whatever it was for the first workbook opened in that excel session and the user will get the infamous Circular reference error. You'll need a macro anyway to make sure this will not happen.....

    Or make sure this workbook is always the fiorst to be loaded

    Also, you would need a way to reset the workbook, otherwise there would be no way to empty the cells from their values. For that one would use an extra cell (say Z100) and include the value of that cell in the formula (lets assume this is cell B2):

    =IF($Z$100=1,"",IF(A2-Today()>=0,OtherSheet!Targetcell,B2)

    Now if you want to reset the values in all cells simply enter a 1 in cell Z100.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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