Michael Lees
New Member
- Joined
- Jan 1, 2004
- Messages
- 3
People,
I have a spreadsheet in which two columns contain respectively the Category and the Sub-Category of expenses (e.g.: Car and Mechanic, House and Furniture, etc.).
Since all expenses are calculated and displayed in another sheet, I MUST avoid entering non-existing Categories and Sub-Categories. To do this, I am using a Validation drop-down menu for the Category cells and the Sub-Category cells (each cell will show a drop-down with all the options).
The problem is that not all sub-categories correspond to all categories. That is, Furniture is NOT a sub-category for Car!
The way things are right now, I can make a mistake and enter an invalid sub-category for a certain category (since the Sub-CAtegory drop down shows ALL sub-categories, not only the ones for the value in the Category column).
My question is: how can I have the drop-down in the Sub-Category column display only the options for the value selected in the next-door cell, in the Category column? That is, have the values in the drop-down vary according to the value entered in the cell next-door.
I tried using the OFFSET, ADDRESS and CORRESPOND function, and did get something, but nothing that can be put in the VALIDATION configuration.
Thanks for ANY help!
I have a spreadsheet in which two columns contain respectively the Category and the Sub-Category of expenses (e.g.: Car and Mechanic, House and Furniture, etc.).
Since all expenses are calculated and displayed in another sheet, I MUST avoid entering non-existing Categories and Sub-Categories. To do this, I am using a Validation drop-down menu for the Category cells and the Sub-Category cells (each cell will show a drop-down with all the options).
The problem is that not all sub-categories correspond to all categories. That is, Furniture is NOT a sub-category for Car!
The way things are right now, I can make a mistake and enter an invalid sub-category for a certain category (since the Sub-CAtegory drop down shows ALL sub-categories, not only the ones for the value in the Category column).
My question is: how can I have the drop-down in the Sub-Category column display only the options for the value selected in the next-door cell, in the Category column? That is, have the values in the drop-down vary according to the value entered in the cell next-door.
I tried using the OFFSET, ADDRESS and CORRESPOND function, and did get something, but nothing that can be put in the VALIDATION configuration.
Thanks for ANY help!
