Use results of VLOOKUP to auto populate a validated list

[LKinane]

Hi,

I would like to use a combination of a VLOOKUP and a validated list. e.g. the choices in the validation list are either "internal" or "external" (methods of delivering courses). The courses can be delivered either way but we have historical data which tells us what is the usual method, which I would like to use as the default value in the validated list. I cannot seem to use both at the same time.

Any help would be much appreciated.

Thank you

 

[JayBurn]

Use an IF in the Data Validation?

 

[JayBurn]

Alternatively, use named ranges...

So,
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Data Validation 1
[/TD]
[TD]Data Validation 2
[/TD]
[TD]Internal
[/TD]
[TD]Internal
[/TD]
[TD]External
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD][/TD]
[TD][/TD]
[TD]External
[/TD]
[TD]Int A
[/TD]
[TD]Ext A
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Int B
[/TD]
[TD]Ext N
[/TD]
[/TR]
</tbody>[/TABLE]

Create a named range of C1:C2 as Choice1, D2:D3 as Internal, and E2:E3 as External (case sensitive).
In A1, use Data Validation as List, and the Source enter =Choice1
In B1, use Data Validation as List, and the Source enter =INDIRECT(A1)

Done.

 

[LKinane]

Thank you for responding but I think that maybe I did not make myself clear.
In my file, in Column D, a course name is entered (also via a validated list).
In Column E, I would like to add the results of a VLOOKUP which results in the cell being auto-populated with either Internal or External.
The person entering the data can have the option to change this to the other [Internal or External] choice, but I would like this to also be validated so that only the actual word "Internal" or "External" can be entered in column E. (This is because the results of Column E are used in another formula, which needs to be exact (not abbreviated etc.))
Thanks again. I hope that makes sense.

 

[JayBurn]

You can still use a named list, but make the cells populate from the selection...
So make a named range (assuming column F is empty) for F1:F2;
F1 ="Default - "&VLOOKUP( your lookup )
F2 =IF(VLOOKUP( your lookup ) = "External","Internal","External")

That way the range will throw out "Default - ~whatever the course is~" with the opposite available on the drop down as the first option. Then where you need to use the Internal or External data in the formula you can use RIGHT(cell ref,8) and it'll just you either Internal or External without the default. Or just use the VLOOKUP in F1 if you'd rather it didn't have the Default tag...

You can use VBA to do an Application.WorksheetFunction to auto-populate it when you make a selection on Column D, my system is chugging working on a big chunk of data at the moment otherwise I'd dig out the one I did recently with the assistance of Fluff, the thread is here: https://www.mrexcel.com/forum/excel-questions/1020237-vba-lookups-based-cell-input-exit.html if you feel comfortable editing the code to fit.