Cascading Dependent Validation Lists needs macro

Hello Gurus,

I'm using XL 2010-2013 to create a vacation day accrual spreadsheet. Our Fiscal year starts in July and Floating Days are accrued. There is a day gained in July, November and February. If the day gained in July is not used in that month, it is still available in August and so on. I have a Data Validation list assigned to cell E6 (for July) with the choices of “Available” and “Used”. INDIRECT($E6) is set for August (cell H6) and INDIRECT($H6) is set for September (cell K6). If I select “Used” for $E6”, I need “Used” to cascade down to every month’s validation list after July. “Used” is the only option in the dropdown but “Available” appears in the cell. Selecting “Used” for any month should force ALL following months to “Used”. If I select “Available” upstream, all downstream selections should be forced to “Available”. I’ve exhausted all non-VBA options that I can think of with no success. After researching, I’ve found the following code but can’t get it to work properly. My validation lists are not all in consecutive columns like the range below. Also, it only works if I make a selection for July ($E$6), selecting a value for a month such as “September” has no effect on “Oct,Nov,Dec …” I need a way to specify all the columns that have a validation list and need to continue the logic for the days earned in November and February. It also has to work every time I add new rows for additional employees without having to manually modify reference cells (there will be thousands of rows).

Please Login or Register  to view this content.

Thanks in advance,
(sample file attached)