Logic statements/Pulling data

[scdiverjba]

In one spreadsheet I have two different tabs. I would like Tab A to pull data
from Tab B (contained in the same file). On Tab A, I have added a dropdown
menu that a user selects a state. On Tab B I have a many different cells I
would like to pull data from for each state. For example, if I choose
“Indiana†in the drop down menu, I would like the report in Tab A to prefill
with information (pulled from Tab B) pertinent to Indiana. If I choose
Florida, then Florida and so on. Tab B (the data) contains information from
years 2004 – 2008.

How do I go about having cells in Tab A, pull the correct data from Tab B by
specific state? What function do I use and how do I use it?

Thank you.


jba

 

[Hong Quach]

Hi jba,

From the description you provided, I assume that the data in TabB (Sheet2)
is in the format of [STATE][INFO1][INFO2]...{INFOn], and in TabA (Sheet1) you
have a single cells that contain the name of the state exist in [STATE]
column in TabB.

TabB (Sheet2)
| A | B | C
1 | State | Info1 | Info2
2 | Indiana | 12321 | IIIII
3 | Texas | 3232 | TTTTT
4 | Washingon |3468 | WWWWW
5 | Ohio | 53158 | OOOOO

TabA (sheet1)
| A | B | C
1 | State | Info1 | Info2
2 | Texas | =VLOOKUP(A1,Sheet2!A2:B5,2,FALSE) |
=VLOOKUP(A1,Sheet2!A2:B5,3,FALSE)

Changing value in A2 of TabA will change the value in B2 and C2 of TabA
because it use the VLOOKUP function to look up the information on TabB. Cell
A2 can be a drop downlist as you describe. (FYI: You can't specify data in
other sheets as the source for list in data validation.)

Hong Quach