Hi Mike
use a combination of data / validation to create the drop down list and
VLOOKUP function to populate the related information
assuming that in Sheet2 you have the following
A B
1 State AgencyRate
2 WA 10.00
3. SA 15.00
4. NT 20.00
(yep, i'm probably in a different country, but you get the idea)
now select from A1 to the end of your state's list and choose from the ment,
Insert / Name / Create - ensure Top Row is checked and click okay - you've
created a named range called "State"
now select from A1 to the end of the AgencyRate list and click inside the
name box (left of formula bar) and type ARates and press enter - we've
created a second named range.
Now click in the cell where you want your drop down list to appear and
choose Data / Validation from the menu - in the settings tab, choose List
from the Allow drop down box and then click inside the white box under this
and press the F3 key - this will bring up a list of your range names, choose
State and click Ok. You will now have a drop down list in this cell.
Now click in the cell where you want the agency rate to appear and type
=VLOOKUP(A1,ARates,2,false)
where A1 is the cell reference with your drop down list in it - you can use
the F3 key for the ARates bit too.
now choose a state & see the rate appear ... delete the state and you'll get
a #NA error - this can be supressed by embedding your VLOOKUP in an IF
statement e.g.
=IF(A1="","",VLOOKUP(A1,ARates,2,false))
where A1 is the cell reference with your drop down list in it
hope this helps, let us know how you go.
Cheers
JulieD
mike7159 said:
Hi, I was wondering if anyone could help me formatting my spread sheet for
work. I need to enter a state, by selecting it in a drop list, and then the
agency rates for that state automatically shows up in a table that I have
prepared and is ready to be calculated. How do you enter something in one
cell and it automatically results in numbers to appear in other cells?
