I have a new question concerning formulas in Excel. Thing is, I have a sheet, sheet2, containing dates and values. Currently I am using a combination of the index() and match() functions in to fetch different values, according to certain conditions - one being the date, from sheet2 into another sheet, sheet1, .
The data in sheet2, will be updated from time to time wherefore certain dates in the data will obviously "disappear" and of course, yield an error in the index-match formulae, not being able to find the value at hand.
My question is then, if there is an easy way to write the formula so that if the date we try to find from sheet1 is less than the current date, transform current value into constant value. The pseudo code would be something like,
IF date_to_find from sheet1 IN sheet2 > todays_date Then
Set value to constant (do not evaluate formula)
Else
Find value in sheet2 where the dates are matched in both sheet1 and sheet2
End
I know the easiest way might be to just implement the whole thing in VBA, but just wanted to check if anyone had a nicer solution.
Current formula in sheet1 is
'A872 = "2013-05-17"
'F872 is the goal cell (containing formula)
'$A$1:$K$100 contains date in format "yyyy-mm-dd" (given as column)
'$A$2:$K$2 contains currencies (given as row)
'$A$3:$K$3 contains text (also given as row)
IFERROR(INDEX('sheet2'!$A$1:$K$100;MATCH(A872;'sheet2'!$A$1:$A$100;0);_
MATCH(1;('sheet2'!$A$2:$K$2="EUR")*('sheet2'!$A$3:$K$3="Matching text");0));"")
So, if current date is "2013-05-17", the formula evaluates perfectly and returns, say, 400.000. But tomorrow, "2013-05-18", there is no need to change the cell (2013-05-17 value has past), so I would now like to FIX this value, so that the formula doesn't try and find "2013-05-17" in sheet2 no more. I.e. I want cell F872 to just say "400.000", and not "IFERROR(INDEX(...)"
Thanks, Niklas
