• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

multiple returns on a vlookup?

R

ridgleye

New Member
Good afternoon everyone. First time post here. Have been perusing this site for a couple of weeks and am amazed at the wealth of information. So glad I came across it. I have a question that I can't figure out. I'm an intermediate Excel user. Can get around, use a few different functions, functionally, record some ok Macro's but not much more than that. Issue for me is I'm trying to create a string of functions that I don't know how to.


Worksheet 1

A1: State

A2: City Name

A3: Facility Name

A4: Employees contacted


A1: (i.e. - California)

A2: Obviously more than one city in California (I have no more than 5 cities in any given state). Can I create a VLOOKUP in A2 that will give me a drop down box of all cities linked to the state from A1? I dont' have to use a VLOOKUP - just makes sense this way to a non expert user like myself.

A3: Facility Name will be a simple VLOOKUP from city selected in A2

A4: User will plug in number

MACRO: Will push to Worksheet 3 adding up each employee contacted from the respective State/City/Facility selected (as I have multiple individuals calling same area).


Please help - and thank you in advance!
 
Check out Debra's article on creating dependent lists:

http://www.contextures.com/xldataval02.html
 
hope this give you idea:


A1:A12 = California; B1:B12 = A, B, C, ..., L

A13:A22 = Oregon; B13:B22 = M, N, O,..., V

C1 = the word "State"

C2 = the word "City"

D1 = the word "California"

D2 = a list created under Data validation, List and the formula under source is =IF($D$1="california",$B$1:$B$12,$B$13:$B$22)


Of course this is a way, way too simplistic. but I guess you can make indepth expansion to cover all 50 states and cities you want to show.


Side note: your A2 essentially refers to only 1 outcome on A3 because it is a one on one relationship. So one state can already jump to the facility name without going thru another layer. You can actually combine the facility name with the city name, i think?
 
Luke M - thank you for the link. That helped a lot.


Fred - you are correct with the one on one relationship. Only one facility in any one city. The information was forwarded to me and why they want them split, I don't know. In the end my main focus is them gathering total numbers of employees contacted. Making it as simple (for me) as possible is the goal.
 
You must log in or register to reply here.
Back
Top