Issues With The "IFERROR" functionality...

Wulf

Active Member
Joined
Dec 1, 2004
Messages
395
Office Version
  1. 365
Platform
  1. Windows
I've a rather large amount of data.

In one section (Columns H - U) a daily update of information regarding staff is pasted in.

In another section (AA - AN) is where data from the day prior is held.

Another section (AR - BE) contains all those staffers who have been removed from Columns H - U.

Another section (BJ - BW) is where I have it set up to automatically check the first section against the second and third, and to update any information that has changed.

It is this final updated data that is copied and then moved to a different worksheet for admin usage.

The whole thing works great, with:

Example Formula in BJ8: =IF($BI8="","",IFERROR(VLOOKUP($BI8,$AQ$8:$BE$1219,AB$7,0),IFERROR(VLOOKUP($BI8,$G$8:$U$1219,AB$7,0),VLOOKUP($BI8,$Z$8:$AN$1219,AB$7,0))))
Example Formula in BW8: =IF($BI8="","",IFERROR(VLOOKUP($BI8,$AQ$8:$BE$1219,AO$7,0),IFERROR(VLOOKUP($BI8,$G$8:$U$1219,AO$7,0),VLOOKUP($BI8,$Z$8:$AN$1219,AO$7,0))))

*EXCEPT* The formula I have returns a "01/00/00" (displayed as "01/00/1900") when it finds a blank cell.

This then forces me to "CTRL-H" 5 Columns (BS - BW) to remove all said "01/00/00"s, replacing them with "".

Any ideas on how to make it return a "" (blank, no-characters entered) instead of the "01/00/00"?

Thank you.
Darrell
 
Last edited:

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
If the formula always returns a number, how about
=IFERROR(1/(1/IFERROR(VLOOKUP($BI8,$AQ$8:$BE$1219,AB$7,0),IFERROR(VLOOKUP($BI8,$G$8:$U$1219,AB$7,0),VLOOKUP($BI8,$Z$8:$AN$1219,AB$7,0)))),"")
 
Upvote 0
I tried that, had to modify it just a little, and with:

=IF($BI8="","",IFERROR(1/(1/IFERROR(VLOOKUP($BI8,$AQ$8:$BE$1219,AK$7,0),IFERROR(VLOOKUP($BI8,$G$8:$U$1219,AK$7,0),VLOOKUP($BI8,$Z$8:$AN$1219,AK$7,0)))),""))

It worked!

Only five of the 14 columns are actual dates, so I applied the modified formula to those columns, and we have a WIN.

Thank you!
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top