It would depend on how the data is to be consumed are you trying to get a printout, or is the sheet being processed by another program? If you really can't have another column to contain the code then I'm not sure what to tell you. The usual approach to this is to use data validation to create a dropdown list and have a separate column using VLOOKUP for the code. A spreadsheet cell can contain a way to determine a value (dropdown list, formula, etc.) but whatever value it reaches is going to be the value it shows.Įxcel has forms support with things like combo boxes but I believe the value is still output to another cell. It's two things at the same time: a value, and a user interface presentation of that value. Such a thing is "dead simple" in HTML, but an HTML control isn't built for the same purpose as a cell in a spreadsheet. You can't have a cell display one value but "contain" another value. the value that displays in the cell is the effective value of that cell. A fact of Excel design: what you see is what you get, i.e. I'm starting to see what you mean by "without a helper column", but I'm not sure you can get exactly what you want. In the code column use the VLOOKUP function, keying off of the dropdown list value. On Sheet 1, in the description column, use Data Validation to make dropdown lists that reference the description column of the list. Make it a named range (helps avoid circular reference problems). On sheet 2 set up your description/code list.
It sounds like Data Validation (allow List) combined with VLOOKUP will do what you want.
0 kommentar(er)
