• 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.

Dynamic, unique and multiple dependent date data validation

S

Sara

Member
Hi Wonderful Excel Experts


I've created an excel table showing weekly financial data that can then be pulled into a dashboard. Each week, 3 more rows are added, 1 for each area with the same effective date.


Period End Dt / Area / Calc / Year / Month

29/12/12 / Area A / XXXX / 2012 / Dec

29/12/12 / Area B / XXXX / 2012 / Dec

29/12/12 / Area C / XXXX / 2012 / Dec

5/01/2013 / Area A / XXXX / 2013 / Jan

5/01/2013 / Area B / XXXX / 2013 / Jan

5/01/2013 / Area C / XXXX / 2013 / Jan

12/01/2013 / Area A / XXXX / 2013 / Jan

12/01/2013 / Area B / XXXX / 2013 / Jan

12/01/2013 / Area C / XXXX / 2013 / Jan


This extends back to 2011, and will continue onwards into the future

I need to create a drop down box in the dashboard so that managers can only choose one Period End Date and it must be in the table.


These dates will only ever be Saturday’s.


To make it user friendly, I need to have dependant drop downs, restricting the dates to the Saturday’s by year and month that are currently in the table. After all who can remember the date of the 2nd Saturday in May 2011 off the top of their head?

This will need to be dynamic as more data is entered.

I only want the date to appear once, rather than the 3 times it will appear in the table.


First Drop down = YEAR

Second Drop down = Month

Third Drop down = possible dates within selected year and month


My company has issues with macros, so I need to do this with formulas.


Where do I start, to create a dynamic, unique and multiple dependent date drop down based from table data in Excel 2007?

I’ve searched google, youtube and here, and while I can get close…I can’t make it work.


Any ideas?
 
Here is the link to the file

https://skydrive.live.com/redir?resid=9D4A15C8F236789E!107&authkey=!ABaNsWjpfjZQOpY
 
Good evening Sara


This is probably one of the best site to find what you are after


http://www.contextures.com/xlDataVal02.html
 
Hi, Sara!


I'm running out of time, so I only modified the date validation list. Check columns G:J in worksheet Lookup and the named range SaturdayList, used in the dashboard. Entering the year it's automatically updated.

The formula for 1st Sat of the year is (with year in H1):

=FECHA(H1;1;7-DIA(DIASEM(FECHA(H1;1;1)))+1) ----> in english: =DATE(H1,1,7-DAY(WEEKDAY(DATE(H1,1,1)))+1)


The link:

https://dl.dropboxusercontent.com/u/60558749/Dynamic%2C%20unique%20and%20multiple%20dependent%20date%20data%20validation%20-%20File%20for%20Posting%20%28for%20Sara%20at%20chandoo.org%29.xlsx


Hope it helps.


Regards!
 
Hi Sara!


Just one more method..


Code: 
=IFERROR(SMALL(IF(WEEKDAY(ROW(INDIRECT((DATE($H$3,$I$3,1))&":"&(DATE($H$3,$I$3+1,0)))))=7,ROW(INDIRECT((DATE($H$3,$I$3,1))&":"&(DATE($H$3,$I$3+1,0)))),""),ROW(A1)),"")


https://dl.dropboxusercontent.com/u/78831150/Excel/File%20for%20Posting%20%28Sara%29.xlsx


Regards,

Deb
 
Hi Sara,

I got interrupted several times while attempting to respond to your post... but I guess better late than never. I am also glad to see that you got a few other responses.

(Due to firewall restrictions on my end, I am not able to see the file you posted.)


Here is how I interpreted your requirement:

1.) You have a list of dates (PeriodEndDates).

2.) You would like to offer three dependent dropdown lists to your users:

2.a.) List of years available in the PeriodEndDates

2.b.) List of months for those years, from the PeriodEndDates

2.c.) List of dates from the PeriodEndDates, matching the selected year and month


My suggested solution uses three helper columns: one for year, one for months, one for available dates.


My test data setup was as follows:

On a sheet called "DataSheet", in the range A1:H13

[pre]
Code: 
Period End Dt 	         Area 	         Calc 	 Year 	 Month	Years	Months	Dates
Sat, Dec 29, 2012	 Area A 	 XXXX 	2012	Dec	2012	12	Sat, Dec 29, 2012
Sat, Dec 29, 2012	 Area B 	 XXXX 	2012	Dec	2013	#NUM!	#NUM!
Sat, Dec 29, 2012	 Area C 	 XXXX 	2012	Dec	#N/A	#NUM!	#NUM!
Sat, Jan 05, 2013	 Area A 	 XXXX 	2013	Jan	#N/A	#NUM!	#NUM!
Sat, Jan 05, 2013	 Area B 	 XXXX 	2013	Jan	#N/A	#NUM!	#NUM!
Sat, Jan 05, 2013	 Area C 	 XXXX 	2013	Jan	#N/A	#NUM!	#NUM!
Sat, Jan 12, 2013	 Area A 	 XXXX 	2013	Jan	#N/A	#NUM!	#NUM!
Sat, Jan 12, 2013	 Area B 	 XXXX 	2013	Jan	#N/A	#NUM!	#NUM!
Sat, Jan 12, 2013	 Area C 	 XXXX 	2013	Jan	#N/A	#NUM!	#NUM!
Sat, Feb 02, 2013	 Area A 	 XXXX 	2013	Feb	#N/A	#NUM!	#NUM!
Sat, Feb 02, 2013	 Area B 	 XXXX 	2013	Feb	#N/A	#NUM!	#NUM!
Sat, Feb 02, 2013	 Area C 	 XXXX 	2013	Feb	#N/A	#NUM!	#NUM!
[/pre]
The "Years" in column F shows the unique years in the PeriodEndDates (col A)

It was created using the following formula (shown for cell F2)

=INDEX(YEAR(PeriodEndDates), MATCH(TRUE, ISNA(MATCH(YEAR(PeriodEndDates), F$1:F1, 0)), 0))

enter with Ctrl + Shift + Enter


The "Months" in column G shows the unique months in the PeriodEndDates for the selected year

It was created using the following formula (shown for cell G2)

=SMALL(IFERROR((SIGN(MATCH({1;2;3;4;5;6;7;8;9;10;11;12},(YEAR(PeriodEndDates)=SelectedYear)*MONTH(PeriodEndDates),0)))*{1;2;3;4;5;6;7;8;9;10;11;12}, FALSE),ROWS(G$2:G2))

enter with Ctrl + Shift + Enter


The "Dates" in column H shows the unique dates in the PeriodEndDates for the selected year and month.

It was created using the following formula (shown for cell H2)

=SMALL(IF((YEAR(PeriodEndDates)=SelectedYear)*(MONTH(PeriodEndDates)=SelectedMonth), PeriodEndDates), ROWS(H$2:H2)*3)

enter with Ctrl + Shift + Enter


For referencing in the dropdowns, I created the following Named formulas:

AvailableYears

=DataSheet!$F$2:INDEX(DataSheet!$F:$F, MATCH(999999,DataSheet!$F:$F))


AvailableMonths

=DataSheet!$G$2:INDEX(DataSheet!$G:$G, MATCH(12,DataSheet!$G:$G))


AvailableDates

=DataSheet!$H$2:INDEX(DataSheet!$H:$H, MATCH(999999,DataSheet!$H:$H))


PeriodEndDates

=DataSheet!$A$2:INDEX(DataSheet!$A:$A,MATCH(999999,DataSheet!$A:$A))


On a second sheet (that I named "DashboardSheet"), I have three dropdowns:

in cell B1, I have the dropdown for Years, and references AvailableYears

in cell B2, I have the dropdown for Months, and references AvailableMonths

in cell B3, I have the dropdown for Dates, and references AvailableDates


the value in cell B1 was named "SelectedYear"

the value in cell B2 was named "SelectedMonth"

(These two are referenced in the formulas on "DataSheet")


Cheers,

Sajan.


P.S. In the sample above, the SelectedYear was 2012, and SelectedMonth was 12
 
Hi Sara,

Since that was a long explanation, here is a sample file:

http://speedy.sh/QQ4KZ/Chandoo-Question-from-Sara-Dependent-Date-Lists.xlsx


I have also included some conditional formatting on the "DashboardSheet" to flag selections that are no longer valid.


Cheers,

Sajan.
 
Thank you so much for an exceptionally fast response!

I'll spend the rest of today (kiwi time) working my way through your solutions and I'll let you know how I get on.
 
Thank you all for your help.

Using Sajan's instructions I have made everything work the way I imagined it.


From here, I think I'll start trying to figure out why it works and research the array formulas and upskill


Cheers again
 
Thanks for the feedback. Glad to help!


If you need any help figuring out any of the formulas, just ask. Please visit anytime!


Cheers,

Sajan.
 
You must log in or register to reply here.
Back
Top