Simple Date Query 00/01/1900

Hi,

Prob a real simple one, but im struggling a bit.

I have a very simple vLOOKUP running between two sheets. I just want to pull the date from sheet 2. But there isnt a date in one of the cells, so it returns thedate value 00/01/1900. I would like it to either say 0 or be completly blank. Ideally blank though.

From the report I run and input, if the site has not been brought on air there will not be a date, so I cant just manually put the 0 in otherwise that would defeat the object

Many Thanks in advanced

Try

=IF(VLOOKUP(A2,Report!A1:B5,2,FALSE)=0,"",VLOOKUP(A2,Report!A1:B5,2,FALSE))

Originally Posted by oeldere

For excel 2007 and higher you can use this one.

Please Login or Register  to view this content.

That would only blank out errors not empty results where a match was found in the first column of the lookup, however

Please Login or Register  to view this content.

would work.

@crispybadger

Remember to use absulute references ($A$1) with your lookup table, or the range will move as you copy the formula down and leave you wondering why the results are not what you expect.

VLOOKUP(A2,Report!$A$1:$B$5,2,FALSE)

Perfect!!

But now I had another column which has another simple formula that was adding 10 days to the date. How do I keep this blank tto, until a date is input into the B column

Thanks in advanced

Try

=IF(B2="","",B2+10)

Originally Posted by jason.b75

That would only blank out errors not empty results where a match was found in the first column of the lookup, however

Please Login or Register  to view this content.

would work.

@crispybadger

Remember to use absulute references ($A$1) with your lookup table, or the range will move as you copy the formula down and leave you wondering why the results are not what you expect.

VLOOKUP(A2,Report!$A$1:$B$5,2,FALSE)

Hi,

Why would I want to keep absolute values? If I add further sites would I not want the cell values to change when I drag down the formula, thus allowing me to add more sites to the sheet??

Many Thanks

Yoes, but then the table only copies relative to the formula, if you look at the 3 formula in your sample workbook, the whole table moves down not just the end.

If that is going to work for you then it suggest that the master will be an exact copy of the report, in which case why not just enter =Report!A3 in A2 of the master sheet then copy right and down, using conditional formatting to mask cells with a 0 value?