Stop Updating

[nappy1001]

I have a spreadsheet that uses the date from another spreadsheet but only when between certain dates.

=IF('[CPD Record Team 4.xlsx]NP East 1'!$V$3=MEDIAN('[CPD Record Team 4.xlsx]NP East 1'!$V$3,B1,C1),'[CPD Record Team 4.xlsx]NP East 1'!$V$3,"No")

So if the value in CPD Record Team 4.xlsx]NP East 1'!$V$3 is between the dates in b1 and c1 then it displays the date otherwise it come back No.

What I want is for this formula to only "Run" if it between dates b1 and c1. The reason for this is it is a training record the CPD Record Team 4.xlsx]NP East 1'!$V$3 cell will keep on being updated outside the range of b1 and c1 but I want to keep any dates within the range otherwise any changes outside the range will turn the cell to no and loose any dates that have been returned

[tony h]

The simplest way is to set "calculation options ... manual" and only perform the calculations when in date.

You could automate it using VBA to use something like the workbook_open event to check the date and perform the calculation. But this would mean people have to have macros allowed in their security settings.

[nappy1001]

The problem is when it does refresh and perform the calculation it will still update all the cells. Unless you can think of a way setting the update on only particular cells ie

if a1 is between dates b1 and c1 then update

[tony h]

So, are you saying you want some cells to update always and some only when the date is within dates?

[nappy1001]

What I have is a training record with a log of training completed. On another sheet a 7 week training plan which is then rotated round 7 times. So what I want is dates from the training record to populate the training plan which I can do the problem is the dates for the first 7 weeks would continue to update even past the 7 week referance period. So I need to the formula only to work during the 7 week referance period.

Does that make sence??

[tony h]

It would make sense to attach the spreadsheet

[nappy1001]

I think I have done it. This is an example of what I want I think it should make sence
Copy of Excel help.xlsx

[tony h]

You haven't included any sample data from "the other sheet" nor have you included the formulae (or VBA) you are currently using which makes it very difficult to understand what you are tring to do.

If "the other sheet" has data for all records in it then you will just need to include the dates in the lookup. If not I can't see how to get the first values to persist using formulae.

It could be done using VBA