is it possible to lookup a cell value and populate a drop down box with the results in a separate cell

0
votes

I have a google sheet with all current staff information. I have a second sheet for when some piece of data needs changed. Last name, Location, job ect.

on second sheet I am looking to have someone:

  Enter the user's first name in column B, 
  and or Last name in Column C,

I would like a drop down box generated in column D from the given info to contain all usernames found in the sheet with current information for all staff that have the given first and or last names provided.

Once a username has been selected, I can get all the pertinent data from that user with a vlookup from the sheet containing all staff info. For example if someone typed Scott in column B, I would like a drop down generated in column D with all staff that have a first name of Scott. Likewise if the last name was supplied and of course if both first and last was supplied.

1
google-sheetsdrop-down-menuvlookup
share a copy of your sheet – player0
You can use INDIRECT() function to create that, if you share copy of sheet with what you want to achieve it will help – Shiva
docs.google.com/spreadsheets/d/… – Scott Croskey

1 Answers

1
votes

Validation need to be done that's it,

1) Go to Staff Changes'!B2 and Data Validation> List from range & choose range from 'Staff Info'!B2:B

2) Select cell'Staff Changes'!D2 and Data Validation> List from range & range will be filter formula i.e 'Staffing Changes'N2:N.

Result

result2 result1

Few tweaks in vlookup: I am not sure if you need username column twice if you don't need then change your vlookup formula from =vlookup(D2,'Staff Info'!A:K,10,0) to =VLOOKUP(D2,'Staff Info'!$G$1:$K,2,0)

Please provide access to trix in future so that changes & result can be shown in it.

UPDATE:

Use data validation as before & hide filter colum or use seperate sheet for filter formula & use query function in cell A3 =QUERY('Staff Info'!A:J,"select ' ',A,F,' ',' ',G,' ',I,' 'where A='"&A2&"' AND F = '"&B2&"' LABEL ' ''Date',' ''Current Last Name',' ''New Last Name',' ''New Building',' ''New Classification'",1) result wil be something like this enter image description here

QUERY