Linking fields

[gosig]

I am trying to put together a data entry template for flora survey data. I have created a dropdown list to select scientific name of flora species, linked to master list in a separate worksheet. I want other fields (such as Family Name and Common Name) to automatically fill as per the master list, based on the scientific name selected. For example purposes, if I select ‘Abutilon fraseri’ from the dropdown list for Scientific Name, I want ‘Dwarf Lantern-bush’ to automatically appear in the Common Name field as it does in the master list. I hope I have explained this clearly enough. I have played around with VBA code (e.g. trying VLOOKUP, IF, MATCH, GROUP, dependent list functions) for hours, and looked at forums but can’t work it out. Any help greatly appreciated.

 

[Jerry Sullivan]

Welcome to the Board!

If I'm understanding you correctly, you don't need to use VBA code.
You would just need a lookup formula such as a VLOOKUP formula in each of those fields.

For example, the formula in the Common Name field might be:
=VLOOKUP(A2,'Lookup Sheet'!A:B,2,FALSE)
where A2 is your dropdown list cell
'Lookup Sheet' has your Master List in Column A and the Common Names in Column B

 

[gosig]

Thankyou so much! That worked a treat. In some instances, a species does not have a common name. Using this formula, they are given '0'. Is there anyway to write the formula so that the field is left blank in such a case?

Again, many thanks. I never expected such a prompt reply!

 

[Jerry Sullivan]

If you have Excel 2007 or later, you can use the IFERROR function...

=IFERROR(VLOOKUP(A2,'Lookup Sheet'!A:B,2,FALSE),"")

For earlier versions you would use ISERR

 

[gosig]

ADVERTISEMENT

Perfect. Many, many thanks!

 

[Jerry Sullivan]

Sorry, I was answering what to do if the Scientific Name selected isn't found in the lookup.
That shouldn't happen since you are using that as the source of the DropDown list.

The way you can avoid the 0 is

=IF(VLOOKUP(A2,'Lookup Sheet'!A:B,2,FALSE)=0,"",VLOOKUP(A2,'Lookup Sheet'!A:B,2,FALSE,2,0))

Alternatively, you can use Excel Options to setup your worksheet not to display zeros in blank cells.

 

[gosig]

ADVERTISEMENT

I don't seem to be able to get the last formula to work. The linking of scientific and common name worked a treat, but I'm having trouble linking other fields back to scientific name (e.g. family name, legislative rating etc). I am assuming that perhaps the linked fields need to be side by side left to right in the master list?

Is there a way to link multiple fields, ie, such that based on the entry for scientific name, the fields for common name, family, conservation status all auto-fill, regardless of the order in which they appear in the datasheet. The way I currently have the data sheet set up, i have columns to be auto-filled to both the left and to the right of the scientific name column (in the same order as they appear in the master list).

 

[Jerry Sullivan]

Yes, you just need to expand the number of columns in your lookup range along with the Index number of which column to look up. For example if Conservation Status is in Column D, and Scientific Name is still in Column A, this formula can be used to lookup Conservation Status.

=IF(VLOOKUP(A2,'Lookup Sheet'!A:D,4,FALSE)=0,"",VLOOKUP(A2,'Lookup Sheet'!A:D,4,FALSE,2,0))

If you check out the VLOOKUP in Excel's help, this will probably become clearer.

 

[gosig]

Thanks Jerry, will do. I should be able to work it out from here. Thanks for getting me started.

 

[Jerry Sullivan]

Thanks Jerry, will do. I should be able to work it out from here. Thanks for getting me started.

You're very welcome