Complex Vlookup and List Validation and Nested IF statements

I have one sheet where I want the user to use a drop down box to pick the
state, then lower on that sheet I have 6 named arrays consisting of 2 columns
each, Labor Category and Bill Rate, on a second sheet, I have a list of
employees with matching Labor Category names, and bill rate column, I need
the user to pick the state, and the bill rates to be obtained from the
corresponding Array and update the bill rate column on the second sheet? So
I can use one spreadsheet to price mulitiple jobs for mulitple states at
different rates by simply changing the drop down box value containing the
state name.

I have the drop down box to pick the states. And I can use a Vlookup
formula, like =VLOOKUP(D94,CAONSITE,2,FALSE) In this formula D94 contains
the labor category and CAONSITE is one of the named arrays on the other
sheet. This formula works, I need to find a way to use that STATE drop down
box, to change the "CAONSITE" value in this formula. each time I try I get an
error. Is this possible?

Anyone have any ideas?

So, assuming CAONSITE is for California OnSite, and ORONSITE would be for
Oregon?

If the state dropdown has two-char state codes, and state cell is B2, then

=VLOOKUP(D94,INDIRECT(b2&"ONSITE"),2,FALSE)


"Bobby" wrote:

> I have one sheet where I want the user to use a drop down box to pick the
> state, then lower on that sheet I have 6 named arrays consisting of 2 columns
> each, Labor Category and Bill Rate, on a second sheet, I have a list of
> employees with matching Labor Category names, and bill rate column, I need
> the user to pick the state, and the bill rates to be obtained from the
> corresponding Array and update the bill rate column on the second sheet? So
> I can use one spreadsheet to price mulitiple jobs for mulitple states at
> different rates by simply changing the drop down box value containing the
> state name.
>
> I have the drop down box to pick the states. And I can use a Vlookup
> formula, like =VLOOKUP(D94,CAONSITE,2,FALSE) In this formula D94 contains
> the labor category and CAONSITE is one of the named arrays on the other
> sheet. This formula works, I need to find a way to use that STATE drop down
> box, to change the "CAONSITE" value in this formula. each time I try I get an
> error. Is this possible?
>
> Anyone have any ideas?

The Indirect() function was what I needed. Thank you so very much.

Bobby