Hello. Sorry if this has been asked before, but I can't find how to do it anywere.

The problem is the following:

I need to be able to autofill a drop-down list if some cell is not empty.

To be more clear, suppose that i have a drop-down list


in cell B1.

Now, suposse that cell A1 can have a catalog code A, B, or P, or be empty. If it's empty, I want B1 to be the drop-down list, else to be the item specified in A1.

I've narrowed to this:
and in the Data Validation Box, under "Source" I've the following
This works well, except the drop-down shows an empty cell and one has to manually select Apples or Bannanas or Pinapples, wich defeats the whole purpose.

Any suggestions so cell B1 is automatically filled when A1 is specified, or a drop-down if not?

I do this frequently in my forms, the thing to remember is that this solution will be erased by the use of the drop down, ok?

So, in B1, put the formula:

=IF(A1="", "", LOOKUP(A1, {"A","B","P"}, {"Apples","Bananas","Pineapples"}))

Now, as soon as A1 is filled in, B1 will automatically display the answer via this formula.

However, your Data Validation might still be available based on how you set it up. Regardless, if anyone ever uses the data validation drop down list, it will permanently enter a choice into that cell and the formula will be deleted. That may be just fine, it is in my forms since each form is "one use" only and the agents start with a fresh clean form each time with all formulas intact to start with.

Just my idea.