G
Guest
Hi there,
Once again, I would like to tap the infinite knowledge of Excel out there.
I am using a VLOOKUP function to bring in dates from another worksheet. I want my dates to end up in the 8/04/04 format. The trouble is that because I am using VLOOKUP with a sheet with incomplete data, some values are zero, and others are #N/A. For example, I am trying to pull in the dates that a particular product has sold. If a product had not sold, its 'date' field would be blank, and VLOOKUP returns a zero value. If it's not in the table at all, VLOOKUP returns a #N/A value.
To combat the #N/A problem, I use this:
=IF(ISERROR(VLOOKUP(A2,Sheet3!$B:$P,4,FALSE)),0,VLOOKUP(A2,Sheet3!$B:$P,4,FALSE))
This formula prevents any #N/As from being displayed, and returns 0 instead. This is great for a lot of work that I do, but here, with date format, it returns 1/0/00 every time a zero value is recorded.
What I want to have occur is for my master sheet (the one that has the VLOOKUP) to either display a real date (5/21/01), or no date at all. 1/0/00 is probably the LEAST desirable value that could be in that cell. I am also happy with a '-' (the zero value in accounting format).
I am trying to use this with a macro that creates a report, part of which is this date field from the VLOOKUP. Because I do not sort by date in the end, I just see this litany of 1/0/00 results throughout my table.
Thus, is there a way I can use an IF statement (even with ISERROR) to keep a cell empty (or suppress its data) while retaining the date format? How does everyone else deal with zero values in their Date fields?
Thanks so much in advance!!!!
Once again, I would like to tap the infinite knowledge of Excel out there.
I am using a VLOOKUP function to bring in dates from another worksheet. I want my dates to end up in the 8/04/04 format. The trouble is that because I am using VLOOKUP with a sheet with incomplete data, some values are zero, and others are #N/A. For example, I am trying to pull in the dates that a particular product has sold. If a product had not sold, its 'date' field would be blank, and VLOOKUP returns a zero value. If it's not in the table at all, VLOOKUP returns a #N/A value.
To combat the #N/A problem, I use this:
=IF(ISERROR(VLOOKUP(A2,Sheet3!$B:$P,4,FALSE)),0,VLOOKUP(A2,Sheet3!$B:$P,4,FALSE))
This formula prevents any #N/As from being displayed, and returns 0 instead. This is great for a lot of work that I do, but here, with date format, it returns 1/0/00 every time a zero value is recorded.
What I want to have occur is for my master sheet (the one that has the VLOOKUP) to either display a real date (5/21/01), or no date at all. 1/0/00 is probably the LEAST desirable value that could be in that cell. I am also happy with a '-' (the zero value in accounting format).
I am trying to use this with a macro that creates a report, part of which is this date field from the VLOOKUP. Because I do not sort by date in the end, I just see this litany of 1/0/00 results throughout my table.
Thus, is there a way I can use an IF statement (even with ISERROR) to keep a cell empty (or suppress its data) while retaining the date format? How does everyone else deal with zero values in their Date fields?
Thanks so much in advance!!!!