Dependent Data Validation Using Unsorted List

Hi All,

I've learned how to do dependent data validation using sorted list. Now the problem comes when i want to use unsorted list. I can't sort the list as I need to maintain the chronological order. Please refer to the attached file for reference.

In A3:A8 i have values for accrual ID. The accrual ID is taken from a master budget and the corresponding master budget is presented on cell B3:B8. For each accrual ID, I have put accrual value on cell C3:C8. As stated above, I can't sort A3:A8.

The problem is, let say I have to pay USD 300 that will be paid against the accrual ID (Cell E3). I will use Budget Code on Cell F3. Now, how to do data validation (i.e., providing drop down box) in Cell G3 based on selection on Cell F3?

Please Help.
Accrual.xls

Originally Posted by martindwilson

http://www.contextures.com/xlDataVal02.html gives the best explanation

Thanks so much. The "Using Dynamic List" part seems to (partly) solved the problem. Just need to deal with duplicate entries on the first drop down list.

Originally Posted by aelgadi

>>> If satisfied with a solution, don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)

Yes, will do once I've solved the whole things.

Thanks.

I'm sorry, after playing with it further, it seems that it's not the solution I'm looking for. See, the contextures' approach is to have separate column (the dependent list) for each unique value in column A (the independent list), which is not possible in my case. Instead of:

Column A (category)	Column B (fruit list)	Column C (vegetable list)	Column D (etc.)
Fruit	Apple	Carrot	...
Vegetable	Orange	Celery	...
	Lemon	Beet	...

I'd like to do:

Column A (category)	Column B (list)
Fruit	Apple
Fruit	Orange
Vegetable	Carrot
Vegetable	Celery
Fruit	Lemon
Vegetable	Beet

The contextures' approach is the first table. What I'd like to do is on the second one. It's doable if the list is sorted, but in my case, that's not the option. Anyone can help with that?

Hi Artronnie, this is exactly what I need too. Did you ever get a resolution?

Thanks

Jon

Nope can't do it the way I want it. My temporary solution is to use macro to automatically sort the list by using worksheet event.