Link to home
Create AccountLog in
Avatar of coachjim
coachjimFlag for Gabon

asked on

How can I set up conditional data validation in Microsoft Excel 2007?

  I'm creating a personal project/task manager in Excel.  Part of that process involved assigning each task to one of several categories:
1)  Domain (Family, Health, Business Development, etc.)
2)  Department (within Business Development:  Product Development, High-end clients, Sales, Marketing, etc.)
3)  Goal (within Business Development/Marketing:  Create a newsletter product)
4)  Project (within Business Development/Marketing/Create a newsletter product:  Complete 60 newsletters)
5)  Milestone
6)  Task

Setting aside the possibility I'm being OCD, right now I'm using a Data Validation list (DVL) for the domain level.  I'd like to create DVL's for each other level, but I'd like each list to be unique to the particular combination of Domain, Department, Goal, etc.  

Is this possible?  

Is it practical?

To be clear, what I'd like to be able to do is, having picked the domain from the list of eight options, I'd like to then pick the department from the options for that domain.  I'd then like to have a list to choose from to pick the goal for that department within that domain.

Thanks,  Jim

Avatar of SiddharthRout
SiddharthRout
Flag of India image

Yes it is possible but with VBA.

You will have to trap the Worksheet Change Event and decide what is the current value of the domain and then based on the domain use code to populate the department, goal etc...

Sid
Jim - if you could create a spreadsheet table with all the combinations of
Domain, Departments, etc., (you'll need to do this in any solution), then we could help you with either direct spreadsheet approach or VBA (which is more likely, as Sid states).

Dave
ASKER CERTIFIED SOLUTION
Avatar of patrickab
patrickab
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of coachjim

ASKER

Patrickab:

I'll have to putz with what you've sent to figure it out first.  As for the items in each category, those lists will be dynamic.

Thanks.

Jim
Jim,

You don't need to figure it out. All you need to do is run my macros and let me know it it's the sort of approach that would work for you. If it is then please provide a file with all the options.

Patrick
Patrickab:

I'm thinking of cutting from five levels to three.  I'll play with that and get back to you tomorrow.
I like your plan.

Jim
Patrickab:

I like your approach although I do have a couple of questions.
How/where do I add new items to each category list?

I've attached a version of my file.

I've cut back to three category fields:

Goal
Project
Milestone/Client
Let me know if you have any questions.

Thanks for your help.  I'm jazzed.

Jim
My-Focus-Plan.xlsm
Jim,

I need from you the lists of items in each category before I can take the next step.

Patrick
Patrick

   As I said earlier, this will be a dynamic list.  I could give you what I have right now, but in a week it would be different.

   Is there any way around this?

   Is it something I can easily add to down the road?

Jim
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Coach -

I got a brainstorm for your solution (maybe) while I was reviewing another question.

If you can create a relational database (e.g., has all combinations listed - and you can add to this, later), then you can construct a pivottable on that database

The user can then use the pulldowns to filter where they want to go, with list resets upon filter changes (clears innocuous selections based on the pivottable hierarchy - e.g., Create 60 newsletters won't be a user option if he's not filtered to create a newsletter project).

Nice thing about this is you can visualize selection options as you go - and the user might go different directs as a result of what's visible at the end.

once the user sees what he/she wants, he/she can double-click that item, the macro can trap that and go to the database to create new entries for milestone/item and whatever else.

Create the sample database and patrick and I can work different paths for you to have options.

The relational database is just a list like you stated in your question - going across with headers, and all combinations listed (or just create enough to get a meaty result)

Dave
Everybody:

Sorry I was away from this project for a day.

Attached is one attempt to provide what I think you need.  I don't know if I'm providing you enough to work with and this is something that I need to be able to update this as my world dictates.

Thanks again for your help and happy new year to all.

Jim
Data-Base-for-Experts-Exchange.xlsm
Jim,

At long last my offering is attached. I've been away for the last 3 days - thus the delay.

On the 'Choices made' worksheet, press the button, make all 3 choices and press the appropriate button. You will then get a little 'reminder' to specify by when you are going to complete those tasks.

I have added Conditional Formatting to column E in the 'Choices made' worksheet so at to give you a visual prompt for what you have achieved on time, you're late on or that you might be in danger of missing your target date.

You can add or remove anything from the Database worksheet, so long as you keep to the same scheme as you have used so far. The VBA code will cope with all the changes you will want to make - as the VBA ranges are all dynamic.

It's all in the attached file.

Hope it helps

Patrick
Multi-choice-01.xls
Jim,

ps. A Very Happy New Year to you and yours.

Patrick
Patrick:

Whoa.  Cool.  I'll play with it and see if I can add the other columns I need around it without blowing it up.

Elegant.  Thanks and happy new year. (Go Badgers...from a Gopher).

Jim
Jim,

> I'll play with it and see if I can add the other columns I need around it without blowing it

The VBA code that I have written will only cope with the 3 columns that you had in the Database worksheet. If you want more columns that will require a re-write, so for the moment can I suggest that you confine any refinements to just improving what we've got so far. If you then want to add something new I suggest that you start a new question - if that's OK with you.

Patrick
Patrick:

The fourth column is the next individual action or task.  Since it is always unique I didn't included it earlier.  The objective is to have lists to pick from for the first three elements that will repeat.  That would be a fourth box in your current input window, but one that isn't tied to a data base field.

Additionally, I need a status column that will have a number of options including:

Thinking
Scheduled
Started
Delegated
Completed
Waiting for Reply
Delayed
Cancelled

I've also been working with columns for:

Importance
Urgency
Estimated Time
Type of Deadline
Notes

If this should be handled here in a couple of parts, I'm game.

Please advise.

Jim

Jim,

I have added a 4th ListBox in the attached file. ListBox4 obtains its data from the Database worksheet but there is no relationship of the 'Activity' column D and the rest of the data on that worksheet. You can still change the Activity descriptions as needed.

If you want to expand on this project please start a new question.

Patrick
Multi-choice-02.xls
Patrick:

That's what I figured...another question.

I may not get to this until Monday as I'm making the long drive tomorrow to set up my mother-in-law's new (Xmas) PC.   We're moving her from Windows 98 to 7.

Thanks,

Jim
Patrick:

To close this phase, I need a workable spreadsheet.  I don't know if I can add all the other columns I want available around what you're giving me, so I wonder if you can put yours inside of mine.

Right now I'm using columns A through Q as shown below.  

A  Importance
B  Urgency
C  Procrastination Tendency
D  Goal
E  Project
F  Milestone or Client
G Next Task
H  Date Logged
I   Time  Estimate
J  Type of Deadline
K  Due Date
L  Status
M  Date Closed
N  Additional notes
O  Conclusion Notes
P  Today's Date (label)
Q  3-Jan (actual date)

The values you're setting up go in D, E and F with me filling in a unique task in column G.
The due date goes in K and the status in L.  

I want to be respectful of what you've done, but I need to be able to have the additional data around the key part you're helping me with.

If you tell me it's fair to call this question complete now that the possibility is clear and the path is set and then I pose a second question to get a spreadsheet that I can use, I'll go with that.

What do you think?

Jim
Coach - I think patrick's right and closeout is called for.

Reference this question as you build a new one for other EE experts to help - a great start and you'll get resolution quickly, I'm sure...

Dave

PS - here's how to ask a related question:

https://www.experts-exchange.com/questions/23490084/17-Jun-How-to-ask-a-related-question.html?sfQueryTermInfo=1+10+30+ask+how+question+relat
You get an "A" for a great idea that didn't get me anywhere in my world.
>You get an "A" for a great idea that didn't get me anywhere in my world.

Who is that aimed at? And if that is the case then is was surely up to you to explain what was wanted to provide you with a stage one solution.

As a Premium Service Member as you know you can ask as many questions as you like.
Patrick:

It was aimed at the world.  I had a bad start to the day, was cranky (it was 4:30 am) and disappointed I didn't have something tangible to work with within the tool I'm using.

I should have stayed in bed.

Jim
Jim,

Please let me know how can I help to provide you with something that is useful?

Patrick
Patrick:

I recently posted the second phase question to this project.

I'd appreciate your help with it.

Jim
Jim,

I'm on the case - please look at my most recent comment there.

Patrick