Data Validation - Use formula in cell, if return error, pick from list

[CBauer00010010]

I have a workbook with a sheet that uses VLOOKUP to select they type of goods we buy from a vendor from another workbook.

=IFERROR(VLOOKUP(TEXT($C7,0),'[Type Resource.xlsx]Class Type Resource'!$B$3:$C$9999,2,FALSE), "")

In some cases this will return a null string as the vendor is not on the list I reference. In these cases I need to allow the user to select from a list of valid options.

My first though was to add the formula to the list of options so the user is selecting an automatic formula or a manual override when they get nothing. This did not work as the VLOOKUP is then referencing the cell the formula that is actually in on the second sheet, 'Class Types'!$A$2, instead of the cell it is being displayed in, 'Processed Data"!$E3:$E9999. Is there a way to make excel use the address of the cell the formula is being displayed on instead of the cell that it resides in on my list? I have tried various uses of INDIRECT and ADDRESS but still get the reference to the cell on the list sheet instead of the display sheet.

My second attempt was to copy my formula into all my display cells and add data validation of list with the options to use if VLOOKUP fails. This somewhat works at first. I have all my results, where I see a blank I can click in the drop down and select an override. But I can not then go back to selecting the formula that was all ready stored in the cell. Is there a way to create a validation that allows what is written in the cell all ready, or an option from a list?

 

[Joyner]

Hello and welcome to the board.

I don't think I am quite following what you want to do, but it sounds like something I have done where if the original list won't contain a valid value in the dropdown, use another list. I usually do this using the CHOOSE Function and some defined names. To help you though, I would need a bit more information like layout and what you want returned where. Please reread your post and see if you can clarify with some sample data, layout and expected results.

 

[CBauer00010010]

I have a workbook named "Type Resource" and a sheet in it called "Class Type Resource". The purpose of this sheet is to serve as a reference for LOOKUP to find out what class of goods a vendor sells. This is a screenshot.
Imgur: The most awesome images on the Internet


I have a workbook called "2017 March Supplier Totals" and a sheet in it called "Processed Data". The purpose of this sheet is to use VLOOKUP by vendor # on each row to automatically populate the type of item the vendor sells. This is a screenshot.
Imgur: The most awesome images on the Internet


The formula that I use for VLOOKUP on that sheet is this.
Imgur: The most awesome images on the Internet


In most cases I will get a type back, but if a sale was recorded under the wrong vendor, or a new vendor was added since the last time the type resource sheet was updated I am having it return a null string "".


In these cases I wish to allow my user to select an type from a named range kept on sheet "Class Types" of "2017 March Supplier Totals" work book.
Imgur: The most awesome images on the Internet


I first attempted to accomplish this by setting all of row E on "Processed Data" to validate from my classTypeas list. Having the a2 cell of "Class Types" hold the VLOOKUP formula Replacing "$C3" with "ADDRESS(ROW(),COLUMN()-2)" thinking that when "Processed Data"E3 had the formula selected from the data validation drop down the ADDRESS would return "C3" as it would be running that formula from the "Processed Data" sheet. Instead the returned value is the cell two columns left on the "Class Types" sheet.


The current workaround I am using to have the formula linked above written in the E coulmn of "Processed Data". Then I select the same colun and add data validation by list selecting my classTypes list. The VLOOKUP displays the type if it found it or the null string if it did not. The user can then click on any cell that dose not have a type and select from the option on the classTypes list. I have made the option on that list in all caps so that you can tell when this override method was used.


The problem then is that once the user has selected a type from the list they can not go back to using the formula. In order to get the formula back I must disable validation on the column, copy the formula into the cells, then re-enable list data validation.


I would like to eb able to add the VLOOKUP formula to my classType list and have it use the location of the cell it is being displayed in on "Processed Data" sheet insted of its location at cell $A$2 on "Class Types" sheet.


If that is not possible I would like to have a combination of data validation that says use the formula already in that cell, or allow the user to select from this list.


Would there be a way to use IFERROR that wraps my VLOOKUP to display my classTypes validation list on error instead of the null string I am currently using?