VLOOKUP failure under Data Tools Data Validation - causes problems

[bwearing]

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 ??

[FDibbins]

Hi and welcome to the forum

Please do not upload a picture of your file...rather, upload a sample of your workbook, showing what data you are working with, a few samples of your expected outcome is (manually entered is ok) and how you arrived at that. (exclude sensitive info). Pictures are pretty much impossible to edit, and no-one wants to re-type your data for you

[bwearing]

Hi and welcome to the forum

Please do not upload a picture of your file...rather, upload a sample of your workbook, showing what data you are working with, a few samples of your expected outcome is (manually entered is ok) and how you arrived at that. (exclude sensitive info). Pictures are pretty much impossible to edit, and no-one wants to re-type your data for you

Hi. Yes. Sorry. It was an act of desperation. i had been trying for well over half an hour to type and re-type the small table example i had because the format that i see in the edit mode is not the format that eventually gets posted (looks like forum software strips out multiple spaces and other characters like tabs out of my edited text) - so i was getting a bit desperate. eventually i used dots . . . . . . .. . sorry about the file upload thing

[FDibbins]

Its not the "file uploading thing", we welcome sample workbooks here - makes things soooo much easier to help with....it was the "picture upload thing" lol

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.