Drop down auto populating - need help with formulas

Question

Hi.

I have an excel sheet where I need 2 separate drop down menus, and these once selected then need to auto populate the correct information into a specific cell.

For more information...

Column C is now designed to offer a drop down with 300+ species options (drop down available on every row from row 2 down in column C for ongoing data entry). However, when a species is selected from the drop down in C, it needs to auto populate the opposite row in column B with the correct code for that species (as it's taking hours for me to do it manually!)

I then need similar with locations. Locality is set up the same, with a dropdown of 6 options in column D from row 2 down. Once a locality is selected, I need it to auto populate the opposite row in column E with the correct grid reference. Ideally, these are also colour coded, (background colour filled) but I don't know how to copy that formatting over as well??

I have 3 tabs, one with the species and code info (species codes are in numerical order as this is primary order for listing - species names are in row opposite matched to the correct code but are then obviously NOT alphabetical), one with all the area info (localities listed in alphabetical order and then grid reference listed in opposite row to match site) and then finally the 3rd one is the one with the table headers displayed as I want everything listed when using it and it's on this 3rd one I'm trying to apply the formula. Every month a new tab will be pasted for new data entry (and I need this to continue to work pretty much indefinitely).

This has been foiling me for a while despite trying to look up YouTube help etc.

I would be VERY grateful for the correct formulas to make this work as it's costing me hours every month in volunteer conservation data entry time.

Answer 1

Re:offer a drop down with 300+ species options

I am afraid there are too many options.

Do you have it searchable?

Answer 2

In B2:

=XLOOKUP(C2, 'Sheet 2'!$A$2:$A$500, 'Sheet 2'!$B$500, "")

In E2:

=XLOOKUP(D2, 'Sheet 3'$A$2:$A$7, 'Sheet 3'!$B$2:$B$7, "")

Change Sheet 2 to the actual name of the sheet with the species info, and change the ranges if they are different from what I used.

Change Sheet 3 to the actual name of the sheet with the location info, and also change the ranges if necessary.

You can set up 6 conditional formatting rules for D2 and/or E2, one rule for each locality.

Fill down from B2 and E2.