I have a named range, ('Actual' - J1:K63), however whenever I delete any rows the named range reduces by that number of rows, so whenever I add more rows, it does not update.
Offset would cure this problem, but how do i write the formula within named range?
Re: Offset named range
Thanks for that, however a formula that refers to that dynamic range now does not work.
Please see attached. Column A & B are dynamically ranged 'Possible' and cell D2, looksup D1 against Column A and gives the figure in Column B if there is a match.
Can you help?
Re: Offset named range
Change the 1 at the end of the formula to 2...
=OFFSET(Sheet1!$A$2,0,0,COUNT(Sheet1!$A:$A),2)
Hope this helps!
Re: Offset named range
Thanks for that, now it appears just the first 2 dates do not work, I get a '#VALUE!' error.
Can you help again
Re: Offset named range
What, you mean the first 2 dates that are before the date in D1 ( i.e. the IF will return the string "No" )? That's because you are trying to do a negation on the result of the IF, and trying to do that to a string results in '#VALUE!' error.
Re: Offset named range
As per Glen's explanation, you might try...
=IF(TODAY()<D1,-VLOOKUP(D1,Possible,2,FALSE),"No")
Hope this helps!
Re: Offset named range
Excellent thanx
Don’t have an account yet? Register yourself now and be a part of our community!
