Hi - sorry i am very new to actually posting on a forum - so i hop i have got this right ( and sorry if havent).
Here is a small sample of a worksheet i have. It is for simple data entry and i set up formulas in each cell and dragged them down for several rows - to allow me several rows of controlled data entry:
REF.......... Investor.......... Contract......... Supply
NO........... ID.................. ID.................. Type
V12345..... TW................. INVR.............. TRVL
LC60199.... PJ.................. INVR.............. TRVL
Y2MV88.... JPN................. INVR.............. TRVL
X9898...... JPN................. INVR.............. CONS
.............. #N/A............... #N/A.............. #N/A
.............. #N/A............... #N/A.............. #N/A
.............. #N/A............... #N/A.............. #N/A
.............. #N/A............... #N/A.............. #N/A
etc
Referring to this small sample - the REF NO is a key and there MAY be records/details contained in a table in another sheet - in which case I want the relevant data ( Investor_ID, Contract_ID etc) to be brought into this sheet. I do this with VLOOKUP in each cell above - this works well.
BUT
When the REF NO data is not in the other sheet - I need to enter it in directly here. NO problem - but this means I need to set DATA VALIDATION for the coded fields - for example for the Investor_ID, Contract_ID and Supply_Type fields.
NOW HERE IS THE PROBLEM -
As you can see - I have the nasty looking "#N/A" in each of these "look up cells". This is because the VLOOKUP in each of these cells fails as the REF NO key is empty and so the VLOOKUP is unable find a value. The usual solution to this i understand is to put an "IFERROR(VLOOKUP(.......),"") on the VLOOKUP in the cell. This seems to put some sort of blank into the cell and so the "#N/A" no longer appears. Great.
But in the case above the DATA VALIDATION look up ( a List ) on the cell will then give me an error - "INVALID Supplier_ID".
It seems that the "" character that the IFERROR puts in the cell is not simply a null and so the DATA VALIDATION then gets triggered and tries to validate that character (ie checks it against a List of Valid codes) and fails.
I cannot simply set the DATA VALIDATION - ERROR ALERT to anything else but STOP - because I need to make sure that the data entered into these fields is checked against a valid list.
I want to still have both the VLOOKUP and the DATA VALIDATION in these cells but without the ugly "#N/A" appearing in these fields when the VLOOKUP doesn't find a value.
Can anyone help please ??
Bookmarks