I am working on a time-based spreadsheet (each row represents one period) and
have written a formula that refers to data from a previous row, but I want
the look-back to be variable based on the user's selection of some integer.
Inevitably, preserving the variability means that sometimes the formula
evaluates to nonsense, as in "it's now period 3, but the look back is 10
periods", which results in #N/A appearing in all cells until period 11 rolls
around. (For those who are having trouble visualizing what I'm talking
about, here's the formula:
=VLOOKUP(A5-Recovery_lag,$A$5:BA$364,21) where Column A contains the period
number and "Recovery_lag" is the look-back variable.)

Since subsequent columns rely on the values in the column where this formula
appears, the #N/As now ripple through the entire spreadsheet.

Can anyone suggest how I can get around this?

Thanks!

Write your formula like this:

IF(ISERROR(VLOOKUP(A5-Recovery_lag,$A$5:BA$364,21)),"",VLOOKUP(A5-Recovery_lag,$A$5:BA$364,21))

This says...if the vlookup returns an error (#N/A is an error), then
return a blank, otherwise return the result.

Hope this helps
Christopher


--
Christopher770
------------------------------------------------------------------------
Christopher770's Profile: http://www.thecodecage.com/forumz/member.php?userid=188
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=69780