ExcelBanter - Copy cell value from another worksheet

Tim,

You can easily do this with a worksheet formula. And you can add a drop-
down list in cells B1, C1, D1, etc using Data Validation. This way you only
need choose a state from the list.

In cells AA1, AB1, AC1, etc you can use an Index-Match formula to
calculate the values. This will change each time you pick a different state
from any of the drop downs.

In cell AA1 the formula would look like
=Index(Sheet1!$B:$B,Match($B$1,Sheet1!$A:$A,0),1)
Adjust the Sheet1 and $B$1 for cells AB1, AC1, etc.

With a little work you could build this into a worksheet change event
(macro) that could do the same thing.

The data validation part should be fairly easy to set up. Just go to the
Data
menu and work with it.

--
sb
"Tim M." wrote in message
m...
I have 10 worksheets (Sheet1, Sheet2 etc) that have state names in
A1:A50. In each worksheet there are different values in B1:B50 that go
with each state. In another worksheet called "Master", which also has
state names in A1:A50, I would like to type a state name in B1 and
have it copy the value of Sheet1 Column B that corresponds with the
name of the state. I want that value placed in AA1 on the "Master"
worksheet. If I type a different state in C1 on the "Master"
worksheet, I want it to look in Sheet2 and get the corresponding value
in B1 that matches the state and place it in AB1 of the "Master"
worksheet. I want the same for D1, E1, looking for the values in
Sheet3, Sheet4, etc, placed in AC1, AD1.

Example: If in B1 of "Master" I type Utah, I want the value in Sheet1,
B41 (next to Utah) placed in AA1 on "Master". If I type Alabama in C1
of the "Master" worksheet, I want the value from Sheet2, B1 (next to
Alabama) placed in AB1.

Also, the values next to each state changes in each worksheet, so Utah
may have a value in B1 of 15 in Sheet1 and a value of 36 in Sheet2.

Is this complex? I can copy the values manually, but thought there
might be a code I could use. Hope thats enough info. Thx, Tim.