Look Up Data Type + IF Validation Rule

ExcelMercy

Board Regular
Joined
Aug 11, 2014
Messages
151
Hello,


I would like a Data Type "Look Up". Based on what is selected, I want the next field which is also a Look Up to offer certain options based on what is picked in the first Look Up field.



Example:


Field Name
Data Type
User_IDAutoNumber
User_StateLook Up
User_CityLook Up

<tbody>
</tbody>

__________________

User_State
New York
Tennessee
Oregon

<tbody>
</tbody>

__________________

User_City Look Up:
Column1
Column2
Column3
NashvilleSalemNew York City
MemphisPortlandBuffalo
KnoxvilleEugeneRochester

<tbody>
</tbody>





Anyways, I need something that will make it so based on what option is picked in the User_State. This will determine which column is allowed to be pick from.




I know this isn't correct, but in terms of code this is kind of what I'm after.

= if(User_State = "New York", User_City = Column3, if(User_State = "Tennessee", User_City = Column1, if(User_State = "Oregon", User_City = Column2, "")))


I was thinking Validation area would be the area I should edit?


- I can do either custom entry or look up to a table, it doesn't matter to me.




Any help would be greatly appreciated!
Thank you
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Russell Hauf

MrExcel MVP
Joined
Feb 10, 2002
Messages
1,611
I would have a table that had both State and City in it. The State would be duplicated, of course, but for Salem, Portland, and Eugene, the State would be Oregon for each one. Then you can look up City based on State. Make sense?
 

ExcelMercy

Board Regular
Joined
Aug 11, 2014
Messages
151
Ok, so like:


Table: State
St_State
Tennessee
Oregon
New York

<tbody>
</tbody>



Table: State_City
StC_State
StC_City
TennesseeNashville
TennesseeMemphis
TennesseeKnoxville
OregonSalem
OregonPortland
OregonEugene
New YorkNew York City
New YorkBuffalo
New YorkRochester

<tbody>
</tbody>


Table: User Design View

Field Name
Data Type
User_IDAutoNumber
User_StateLook Up (from table: State)
User_CityLook Up (from table: State_City)

<tbody>
</tbody>



How would I make it so User_City's options are based on what's in User_State's field?





Did you mean filling User_State & User_City solely from Table: State_City? How would I go about removing the duplicates? Plus how would I make User_City dependent on User_State's answer?
 
Last edited:

Russell Hauf

MrExcel MVP
Joined
Feb 10, 2002
Messages
1,611
You could use GROUP BY or DISTINCT to remove duplicates:

Code:
SELECT DISTINCT StC_State FROM State_City;

Where exactly are you wanting to do this "Look up?" So to get only the right values for the selected City, you'd do something like this:

Code:
SELECT StC_City FROM StC_State WHERE StC_State = [your value from a...form(?) that you pass in];

The value could be something like Forms!frmChooseLocation!cboState.Value (I can't remember the exact syntax right now, but should be close)

There are also other ways of passing in the State value. I hope this helps.
 

ExcelMercy

Board Regular
Joined
Aug 11, 2014
Messages
151
Should I be trying to group them using a Query?

I was attempting to to this table-to-table. I want the "Look Up" directly in one of my tables, from another table.


I tried what you suggested, but I'm to much of a novice to get it working. :(
 

Russell Hauf

MrExcel MVP
Joined
Feb 10, 2002
Messages
1,611
I'm sorry - I don't have MS Access available to me most days. If you create the separate tables and link them, that would be a good start.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,842
Messages
5,598,394
Members
414,234
Latest member
grlevesq

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top