Forum Discussion
Formula or Function for drop down list
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.
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
3 Replies
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 informationHaving separate table with codes and names you may use code column for data validation and any lookup function in B3 to return related name.
