SOLVED

Formula or Function for drop down list

Copper Contributor

I'd like to know what formula or function I'll require when I select a different "code" on A3 and it automatically selects the correct name for the B3 column.

UlyValdez_0-1712601599345.png

 

3 Replies

@UlyValdez 

Having separate table with codes and names you may use code column for data validation and any lookup function in B3 to return related name.

best response confirmed by GrahmSchneider13 (Microsoft)
Solution

@UlyValdez 

To be clear, the "separate table" that Sergei mentioned does not have to be an Excel table, although that is frequently a good storage option. Your lookup data can be just a rectangular range of cells. (I have included only Code and Name, as I do not know what other kind of personal data is relevant to your workbook.)


With a data range, you can reference the cells via their cell addresses, as done in my examples, or you can assign names to those ranges (one per column, in this case) to make static named ranges and use those names in your formulas, or even dynamic named ranges that account for rows being added/removed.


And whether you use an Excel table or a data range, the lookup data can be present on the same worksheet or, as in the attached workbook, on a different worksheet.


Although it's not required, I wrote formulas that mostly wrap the lookup functions in an IF function to display an empty string (not the same as an empty value!) in the column-B cell if a Code value is not yet selected into its column-A cell.


See the _Info worksheet for more information

 

1 best response

Accepted Solutions
best response confirmed by GrahmSchneider13 (Microsoft)
Solution

@UlyValdez 

To be clear, the "separate table" that Sergei mentioned does not have to be an Excel table, although that is frequently a good storage option. Your lookup data can be just a rectangular range of cells. (I have included only Code and Name, as I do not know what other kind of personal data is relevant to your workbook.)


With a data range, you can reference the cells via their cell addresses, as done in my examples, or you can assign names to those ranges (one per column, in this case) to make static named ranges and use those names in your formulas, or even dynamic named ranges that account for rows being added/removed.


And whether you use an Excel table or a data range, the lookup data can be present on the same worksheet or, as in the attached workbook, on a different worksheet.


Although it's not required, I wrote formulas that mostly wrap the lookup functions in an IF function to display an empty string (not the same as an empty value!) in the column-B cell if a Code value is not yet selected into its column-A cell.


See the _Info worksheet for more information

 

View solution in original post