I would like to be able to use a drop down box to choose a field but have it related to another column in a different table?

eg.

A Company gets 5%
B Company gets 8%

the above info stored in one table

In another table I'd like to choose B Company and 8% automatically come up or that's the only number shown in the drop down box.

I'm sure this must be possible but not sure how, hope the above details my question well enough!

Hi Emma

As you probably found out you cannot create a Validation list in Excel that gets gata from another sheet.

In the attached file I used a basic formula to bring the company names from the "Percentages" sheet to the "Calculations" sheet and put them away from the main data.

The in the "Calculations" sheet I created a Data Validation list that takes the names from this list and uses them in the "Company Name" column.

Then I created a VLOOKUP that looks at the value in the "Company Name" column and then goes to the "Percentages" sheet and finds the matching Percentage.

This was combined with an IF statement that keeps the Percentage 0 until a name is placed in the "Company Name" column.

Hope this helps

Carlos

Attached files...

Linking Lists from Two Sheets.xls