Data Validation (Dependent Drop Down List)

[ankuun]

I want to say that this is a great website! I've learned a lot from this site but I have been trying to create depended data validation and couldn't find an answer to my problem.


I can create drop down list for people to choose a time to sign-in, however, I can not get sign-out data validation to eliminate the time that is listed before the chosen sign-in time.


Example:

A1:A10 is for sign-in:

8:00 am

8:01 am

8:02 am

8:03 am

8:04 am

8:05 am

8:06 am

8:07 am

8:08 am

8:09 am

8:10 am


B1:B10 is for sign out:

8:00 am

8:01 am

8:02 am

8:03 am

8:04 am

8:05 am

8:06 am

8:07 am

8:08 am

8:09 am

8:10 am


I create a drop down list in C1. I also create a drop down sign-out list(in D1) that depends (using indirect formula) on C1.

I choose 8:07 am from the Sign-in drop down list (C1) but the sign-out drop down list still starts from 8:00 am instead of 8:07 am.


I want sign-out data validation to look at the value in sign-in and eliminate anything earlier then that.

 

[bobhc]

Good evening ankuun


I have uploaded a video which should tell you all you need to know about data valuation

it is a bit over 20 minutes long and 137MB in size

https://dl.dropbox.com/u/75495784/Excel%20Power%20Tips_%20Useful%20Techniques%20for%20Excel%20Lists%2C%20Drop%20Do.mp4

 

[ankuun]

Hi bobhc,


Thank you so much for the video but it did not cover what I was looking for. My problem is little bit more specific and most videos just cover basic drop down instructions.


Thanks again.

 

[NARAYANK991]

Hi ,


It being Sunday , I am sorry I cannot post a solution. You can download this file and check out for yourself how it works.


http://speedy.sh/xW4FF/Dependent-Validation-List.xlsx


Narayan

 

[ankuun]

YES!!

Thank you so much Narayank991, I have been looking for this, forever.

I will try to figure it out but your worksheet is little bit complicated for me.

If you feel like you can explain more in the future, please do!


Thank a lot!

 

[NARAYANK991]

Hi ,


Sure. I'll post a better worked out file , and the explanation here tomorrow morning , say by 3.30 AM GMT.


Narayan

 

[ankuun]

That sounds great, Narayank991.


Thanks again!

 

[mercatog]

Hi ankuun

I'll try here to explain a simplified file done on the one posted by NARAYANK991


We need a range named SIGN_IN with all our times (8:00, 8:01....)


In A5 we will create a validation list based on SIGN_IN (In Source:

=SIGN_IN)


And in B5 we will create the second validation list based on an adequate offset of SIGN_IN (In source: [code]=OFFSET(SIGN_IN,MATCH($A$5,SIGN_IN),0,ROWS(SIGN_OUT)-MATCH($A$5,SIGN_IN))


[code]OFFSET(X, R, C, H, W) means the range X is offseted by R rows down and C columns right and its new height is H and new Width is W (If H and/or W are missed, the new range will have the same height or/and same width as the original range)


MATCH(Y, VECT)

will return then index of Y in the vector VECT


ROWS(Z)[/code] will return the number of rows in the range Z


For exemple

- SIGN_IN is the range H6:H16 (8:00 AM to 8:10 AM)

- We choose in the first validation List in A5 8:07 AM

- ROWS(SIGN_IN)=11

- MATCH($A$5,SIGN_IN)=8

- The formula for the second validation list in B5 will be =OFFSET(SIGN_IN,8,0,11-8)[/code]


The new range begins 8 rows down from H6 and zero columns right with a height of 3 rows and the same number of columns (5th parameter missed)

Thus, the range of the second validation list is H14:H16 (containing 8:08 AM, 8:09 AM and 8:10 AM)


Here the file simplified for understanding (look at the source of the two validation lists) http://speedy.sh/Mq5TK/Dependent-Validation-List.xlsx


Regards.

 

[NARAYANK991]

Hi mercatog ,


Thanks for two things - your detailed explanation , and for the simplification.


I would like to add that all the formulae depend on the SIGN_IN and SIGN_OUT lists being sorted in ascending order ; this ensures that when a sign in time is selected from the first drop-down , and we wish to exclude from the SIGN_OUT list all times less than or equal to this sign in time , all the valid times will be bunched together ; this happens because the list is sorted. If it were not sorted , the valid times would be spread out in random order , and it would require more steps to get a proper second drop-down list.


Narayan

 

[kaushik03]

Hi mercatog and Narayan,


Thank you very much for such wonderful explanation and help us learn the technique.


Regards,

Kaushik

 

[ankuun]

mercato:

Thank you for your simplification. It is exactly what I need it (my level.


Narayank:

Thank you so much for the follow up. No wonder why your "Excel Ninja".


You guys are awesome!

 

[Guest]

which is trampling on the public's right to know.,cheap air jordansAbercrombie Outlet,abercrombie, I will succeed.michael kors handbags,air jordans, on such a winter.
On the stairs out looking under. Zhou Qifeng as well as in the past few Rector should be spanking fishes.com/index. I do not know is ironic retribution,polo ralph lauren, this is not also a very meaning it? if I continue to charge forward,michael kors outlet,org/index I will succeed.Related articles: http://strokipedia. if I continue to try,sac longchamp, do not change.
If you can will it buried in the depths of a familiar song like the two appearances of Health four imageslongchamp.B0.php/User:97238461698#people_walk_only_th http://gaa.This time in Ningbo and on with the vehicle arrived with Yangmei Township in Henghe just listen quietly they say why see born Sadness ? but others can not be reconciled to damage. said one morning.(love sometimes is kind of loyalty) her home for a high-tech door so that Lai Changxing to return will be subject to unfair treatment After 12 years of lengthy and expensive,moncler pas cher,http://www.termbridge.net/mediawiki/index.php/User:Fkgfat8ia2#com_httpmedia_sa,php http://www. Please take away all the sea could recall the once love let me wait a lifetime if you no longer miss the affectionate memories let it gone with the wind if the sea can take away my sorrow enough like take away every river with it all the tears I love Really wucongkaozheng see text.
if I continue to try,http://www.certwiki.com/index.php?title=User:Fkgfth0nw8#OhIs_the_feeling_of,cheap air jordans, a woman all night to accompany a friend to chat phone; marriage. If I persist,jordans shoes,http://www.wynnga.com/members/home, tby China essays I turned the phone's address book. marry a man with a stable temperament.