Is there a way for selections in a drop-down menu to generate different cell values?

HI

Why not place the LOOKUP formula in e.g B2 with an IF statement to give a blank if no matching LOOKUP value, In this case you will always enter data into A2.

Regards

Jeff

Originally Posted by solnajeff

HI

Why not place the LOOKUP formula in e.g B2 with an IF statement to give a blank if no matching LOOKUP value, In this case you will always enter data into A2.

Regards

Jeff

My problem is that I want them to be able to type in B2 on down if their selection in the drop-down doesn't require a corresponding expense. At the same time, I don't want them to be able to type in B2 if there is a corresponding expense: I would then want them to be able to type in B3 on down.

I attached a print screen of what I mean. The drop-down comes from the cells with the green background. The cells with a red-background automatically pop up when they make certain selections. Any info with a regular white background is typed in by the user after the selection is made. I want to know if there is a way for them to be able to choose any selection in any of the green backgrounds (i.e., "Postage" in E5 and "Travel" in I5) and still have the proper values (and typing restrictions) come up underneath them.

HI

Yes you can do that using VBA as a formula is not possible if it is to be overwritten depending upon the value of the drop down.

You can use the the "worksheet change event" macro that will be triggered by a change in the drop down value, this can then fill the relevant cell using the FIND function or Application.Match, Application.Lookup etc.

If you post an example it will be possible to give you a working version.

Regards

jeff

OK here is how my spreadsheet is set up now. Cells A46:A52 are the expenses in the drop-downs, and cells B46:B50 are the automatic expenses corresponding with some of the expense selections. Let me know if there is anything else you need to know.

Thanks so much for your help!

Hi

I have attached your file that includes the code to do what you have asked for.

Please let me know if it is okay.

regards

Jeff

That is almost exactly what I want!

The only problem (which was my fault) is that for one of the expenses (Supplies) there should be two automatically generated expenses with it (General supplies and equipment <2500). So for that one I'd actually want two things to pop up. I didn't want to put a space inbetween cells in A46:A52because it would have shown up in the drop-down menu.

I color coated the cells so the corresponding values I want to pop up are more clear. Thanks for your help!

Hi

Attached is the revised version for you to try out.

Regards

Jeff

That fixed my previous issue, but now there seems to be a couple different problem.

For example, if Supplies is chosen in E5, E6:E7 correctly generate. But if I then change to a different selection in E5, the value in E7 does not go away.

Also, I get a run-time error '13' message if Travel, Consultant, or Equipment is chosen.

Sorry to be such a pain, I appreciate the help.

Hi

Sorry my fault there, here's a simpler version that covers all that you need (I hope).

Regards

Jeff

Originally Posted by solnajeff

Hi

Sorry my fault there, here's a simpler version that covers all that you need (I hope).

Regards

Jeff

That's it exactly! Thank you so much for your help.

If you don't mind me inquiring, is there any resource that could help me make some sense of the source code so I could make small changes down the road if necessary (e.g., if I needed to include a new category of expense to the drop-down list)?

Again, thank you.

Hi

There are a lot of resources for learning VBA try googling for something like "learning vba for excel" and try a few out.

When I have a bit of time spare I will comment out the code in the example so that it makes it a bit easier for you understand.

Regards

Jeff

Originally Posted by solnajeff

Hi

There are a lot of resources for learning VBA try googling for something like "learning vba for excel" and try a few out.

When I have a bit of time spare I will comment out the code in the example so that it makes it a bit easier for you understand.

Regards

Jeff

Thanks, I'm particularly interested in how I would merge two different types of coding. For example, adding code for a ComboBox to the code you gave me in this thread.

HI

I had made some changes to your file and now you can add additional categories to the list without changing any code and also you can have up to 10 expense items for each category.

I have added some comments to the code but please let me know if any parts you are not sure of.

Regards

Jeff

Thanks alot Jeff, I appreciate the comments and it works like a charm.

HI

Substitute the following code and the complete range will be cleared.

Please Login or Register  to view this content.

Regards

Jeff

I appreciate the quick response. I can't thank you enough with this.

Probably stretching it here but I'm wondering...is there any way to conditionally protect certain cells that are in the dynamic named range so that they can only be deleted by the user if the main expense is changed?

For example, if Printing is selected, having the Copier Expense cell protected so it can't be editted but allow editting of Toner and Publicity?

Hi

The short answer is yes, but I will need to dig out your file to check the best way.

Regards

Jeff

HI

Attached is a new version that permits the editing of the monetary values only if the relevant items are indicated with a "x" in the blank cell to the right of the value. All other filled values are locked but all blank cells are editable.

I hope that this is what you are looking for.

Regards

Jeff

I made some slight adjustments to your changes and it is exactly how I want it now. It's pretty amazing what you can do with VBA.

Thanks so much for your help Jeff. I had written off my last request as one that would not be solved, but you came through again!

HI

You are welcome, glad I was able to help.

Regards

Jeff