vlookup if cell is blank 1

[mtdew]

I think this may be an impossible circular reference but I really need a second opionion.

My cell has the following formula in it:

vlookup(c2,equipdata,7,FALSE)

but now I'm told that equipdata will be changing and the original data will be lost. Therefore, the information that the vlookup pulls in on 06/01/2011 needs to stay and only if the cell is blank or 0, etc., then does the vlookup need to occur when the sheet is opened on 06/10/2011, 06/15/2011, etc.

Is that possible? I was thinking for cell K2:

if (k2=0,(vlookup(c2,equipdata,7,FALSE),k2)

What do you think?

 

[SkipVought]

hi,

Formulas are dymanic. The evaluate and return data from what is there.

If the data is 'lost' does that mean that it is no longer needed?

Why can't YOU save the data and APPEND new data to YOUR data?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[zelgar]

Your proposed formula will create a circular reference (i.e., if this formula is in cell K2, having the logic see what's in cell K2) which will give you an error unless you change some options in Excel.

Probably the easiest would be to copy & paste special (value) for the cells that currently have data that would be changed when the equipdata is changed.

Also, if possible, could you keep the old equipdata and have a new equipdata listed under a new name (e.g., "newequipdata") and then the cell could be coded as follows:

=if(d2<40696,vlookup(c2,equipdata,7,FALSE),vlookup(c2,newequipdata,7,FALSE))

 

[lionelhill]

Yes, you can use the formula you suggest, but only if you specify that Excel is allowed circular references and set a limit on the number of iterations it should calculate (in your case "1" is adequate).

The result of your formula is that the first valid entry for a particular lookup will be recorded for ever, and any subsequent changes ignored.

But I agree with previous posters: depending on what you are trying to achieve, there may be much easier ways to do it.