select from list
Posted by Steve on December 12, 2001 4:41 AM
I wish to select names from a list of students. The usual Validation>list option does not allow me to have two students with the same name in the list - it always chooses the first instance of the name. Is there any way round this without using a unique number identifier? I want teachers to select student by surname and get a summary of their current progress via a lookup table - this bit I can do but I can't get the surname list to work in the way I want.
Posted by Joe Was on December 12, 2001 5:24 AM
We run into this in matching database records all the time, the general solution is to build a new extended field to match with. The idea is to combine more than one field by concantenating. On a new datasheet or way off to the right of your current sheet build a new field with as many combined parts as it takes to get a unique identifier. Use this data to populate your pull-down list. Use a named range or lable a column and turn on the option "Use labels in formulas."
Rather than "Smith" or "William J." use =A1 & " " & B1 for "Smith William J." then if you need to display only "Smith" strip all the characters from the left up to the first " " [Blank Space]. Several people on this site have shown how to select striped data, but if you need help re-post. JSW
Posted by Steve on December 12, 2001 9:14 AM
Posted by Steve on December 12, 2001 9:16 AM
=MID(AA1,1,SEARCH(" ",AA1,1)-1)
So if AA1 = "Smith William J."
the above formula will return: Smith
Hope this helps! JSW
Posted by Joe Was on December 12, 2001 12:58 PM
How to Strip data with formula
Posted by Joe Was on December 12, 2001 1:02 PM
If your concatenated data is in cell AA1 down then, you can reference and return up to the first space with this formula:
=MID(AA1,1,SEARCH(" ",AA1,1)-1)
So if AA1 = "Smith William J."
the above formula will return: Smith
Hope this helps! JSW
Posted by Joe Was on December 12, 2001 1:08 PM
Note:
Note: If your data is in the format "Smith, William J."
Then you will need to modify the formula.
Change " " to ","
or you can change the -1 to -2 both will give the same answer. JSW
Posted by Joe Was on December 12, 2001 1:10 PM
Note:
Note: If your data is in the format "Smith, William J."
Then you will need to modify the formula.
Change " " to ","
or you can change the -1 to -2 both will give the same answer. JSW
