Multiple layer Data Validation

[donjohnson]

Hello,
I am trying to create a variable data in a spreadsheet which will create travel request forms. I cannot use VBA as many of the users have macros disabled due to paranoia. Consequently, I need to use in-sheet functions.

Here is what I would like to do. Starting with a list of cities and approved hotels (1 or more) with their address & phone, I would like to have a function like data validation to drop down a list of cities in a15. Then, once a city has been selected, I would like b15 to have a dropdown of approved hotels for that city. Finally, once the city and hotel have been selected, populate c15 with the address and phone #.

My data range for the selection looks like:
City1 Hotel1 Address1, with city, st,zip Phone1
City1 Hotel2 Address2, with city, st,zip Phone2
City2 Hotel1 Address1, with city, st,zip Phone1
City3 Hotel1 Address1, with city, st,zip Phone1
City3 Hotel2 Address2, with city, st,zip Phone2

I know someone can help me do this inside of the spreadsheet, using arrays or other "tricks", and I really appreciate your help!

Don Johnson

 

[Livia8]

Assuming your list of hotels is not too long, and assuming you have

City1 in A1, City 2 in A2, etc, and Phone1 in F1, Phone 2 in
F2, and so on, you might want to try with this formula (see below) in B8 and dragging it across as far as F8 and as down as much as you need it to (for instance to B123 and F123). A1 would be the cell where you type the CityNo. you are looking for, while the other fields would be populated automatically. If you want to , you might choose to use a separate sheet for the hotel list, but in that case you'd have to tweak the formula accordingly.

=IF($A$8=$A$1,B1,IF($A$8=$A$2,B2,IF($A$8=$A$3,B3,"")))

Hope this helps, if it doesn't (or if you have more than those 6 columns) try to have a look at thread68-865830, it might be useful.

 

[SkipVought]

Take a look at a very powerful function -- OFFSET

How can I rename a table as it changes size faq68-1331

Conbined with the MATCH function you can create dynamic lists on the fly based on a user selection.

Let's use your example and assume that the user has selected City3, the value of which is in sheet1!A1
[tt]
sheet1!A2: =MATCH(sheet1!A1,sheet2!A1:A5,0)
[/tt]
this returns a value of 4, as the 4[sup]th[/sup] row in the lookup table is the first ovccurrence of City3
[tt]
sheet1!A3: =CountIf(sheet2!A1:A5,sheet1!A1)
[/tt]
this returns 2, the number of rows containing City3
in Insert/Name/Define --
[tt]
Name in workbook: rHotels
Refers to: =OFFSET(sheet2!$B$1,sheet1!$A$2-1,0,sheet1!$A$3,1)
[/tt]
the offset function dynamically defines the range rHotels.

Use rHotels as the Input Range or ListFillRange of your second drop down.

Skip,
[sub]
[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken! [/sub]

 

[donjohnson]

It seems that I may not have been clear before.
The data looks almost like this:

Huntington   Best Western  3441 Rte 60E     736.9772
Huntington   Comfort Inn   249 Mall Road    733.2122
Mt. Hope     Holiday Inn   340 Oyler Ave    465.0571
Mt. Hope     Courtyard     124 Hylton Lane  252.9800
Mt. Hope     Country Inn   2120 Harper Rd   252.5100
Pulaski VA   NRV Suites    117 Domino Dr.   674.0780
Salisbury    Holiday Inn   530 Jake Blvd    637.3100
Saltville    Swiss Inn     31 Lee Highway   429.2233

This data is changeable, and entries can be added or deleted.

What I would like is:
In A8, a dropdown (like data validation) to select the city. When the user selects Mt. Hope, and then clicks cell B8, a dropdown of the Mt.Hope hotels allows them to select which hotel. This causes cell C8 to be populated with the desired hotel's address & phone number.
This same process is repeated for up to 4 more hotel entries in successive rows.
This form is used for our management teams to request travel arrangements to be set up.

As stated above, I cannot use VBA, but only native Excel functionality.

Thanks again!

Don

 

[SkipVought]

Don,

Exactly what I was referring to.

Skip,
[sub]
[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken! [/sub]

 

[donjohnson]

Skip, I understand most of what you say, and will be glad to try it out. First, a couple questions. How can I get a list of unique cities for the user to select? I have tried an array formula, but realize that as the table changes, I would need to constantly update the city list.

Second, in this statement:
=OFFSET(sheet2!$B$1,sheet1!$A$2-1,0,sheet1!$A$3,1)
what is the ?

Would it help if I sent the workbook to you so you can see what I am working with?

Don

 

[SkipVought]

1) Select ONLY the City Column - Data/Advanced Filter - Copy to another location/UNIQUE

or

2) Use Data/Get External Data/New Database Query/Excel files -- YOUR WORKBOOK - the sheet containing the TABle -- edit the query -- drag City into the grid -- hit the SQL toolbutton and insert distinct after Select -- Edit/Return data to Excel. After setting up the list, all you have to do is Data/Refresh.

Unfortunately, since you cannot use VBA, the Refresh will have to be manually performed. Personally, I would FORCE the users to enable macros. It can be done.

Second the is a misfired Process TGML -- sorry -- it just
[tt]
=OFFSET(sheet2!$B$1,sheet1!$A$2-1,0,sheet1!$A$3,1)

[/tt]


Skip,
[sub]
[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken! [/sub]

 

[KenWright]

I think Debra Dalgleish also gives you what you want with dependent lists via validation:-

http://www.contextures.com/xlDataVal02.html

Regards
Ken............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[SkipVought]

Ken,

You're Wright, of course!

It had slipped my mind, such as it is ;-)

Skip,
[sub]
[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken! [/sub]

 

[donjohnson]

You all have given me some good things to work on here. I took a quick look at Debra's site, and will pursue that further when I get back from some business traveling.

Thanks for your help - it's great to be part of a team like this!

Don

 

[KenWright]

You're welcome and enjoy the trip

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------