I have two columns of data. the first holds team member names and the second holds their member number. I need to report individual member scores by referencing their member number only. I think that I can use data validation but have not been able to get the results that I need. Can anyone help?
Welcome to the Forum gunsafety !
It's usually better to describe your problem rather than suggesting how you expect to solve it. This does not sound like a data validation problem to me. How do you want to determine the member number? Do you type in a name and need to retrieve the member number? If your two columns of data are in A and B, and you type a member name in C1, this gives you the member number:
If that's not what you mean, then please give a more detailed description of how the data is organized in your file, what input the user will provide and where, and what you want the output to look like and where.
Please Login or Register to view this content.
The best thing would be to attach a file with no private data. The paper clip icon does not work for attachments. Instead, under the text box where you type your reply click the Go Advanced button. On the next screen scroll down and click on Manage Attachments, which will show a pop-up window to Select and Upload a file. Then close the window.
On sheet 1, I have a list of 99 competitors in column A and their member I.D. numbers in column B.
On sheet 2, I have a score report form on which I must enter only the I.D. of those competitors that participated in the match.
I would like to enter a name on sheet 2 and have the correct I.D. number inserted on the report.
This will only work if your names are unique:
Excel 2016 (Windows) 32 bit
ADAM Adam SARAH JAMES BILLY MARIA CAROL DAVID
Sheet: Sheet1
Excel 2016 (Windows) 32 bit
Sheet: Sheet1
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Thaw works fine and I can use it, but for one concern.
Some team members have difficult last names to spell (Kalinichenko, Bianchini, etc.).
It would be better if I could pick the name from a drop-down list, to avoid errors caused by misspellings.
Each match will have up to 40 competitors, so I would like the recording process to be as direct as possible.
Though I have used Excel for a few years, I am not adept at the myriad of functions available.
I have taken no instruction other than basic Excel.
Thank you for your help.
I have created a dynamic named range called NameList. To create the dropdown, I used Data Validation with a List, using NameList as the range for the list.
The lookup is done as in the VLOOKUP demonstrated above.
The formula for the dynamic named range is
Formula:
Please Login or Register to view this content.
This means start the list in A2. The 0,0 means to offset the starting cell by 0 rows and 0 columns, in other words, do not offset the start. The COUNTA formula counts how many names there are in column 1 and "-1" is to allow for the heading. So this range is the list of names.
That works wonderfully.
Thank you very much for taking the time to help me!
You're welcome, and thanks for marking your thread Solved.
If a member helped you solve your problem, consider adding to their reputation by clicking on the star icon addreputationiconsmall.jpg below their name.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks