How do I add this kind of functionality to a drop down list?

Hi
try

in cell C3
=IF(LOOKUP($B3,{"Food","Rent","School";1,2,3})=COLUMN(A1),$A3,"")

then copy to the right upto E3

rgds,
jindon

jindon, that was exactly what I was looking for! Thank you so much, I never would have figured out anything like that in a million years. I'm going to try and customize it now for my spreadsheet. Again, thanks for the help!

Cheers,
Jeff

Re: How do I add this kind of functionality to a drop down l

bovinda said:

I hope this is clear (and if not, I'll clarify!). If you have an alternative suggestion I would also love to hear your ideas!

And any suggestions as to how I could do this or where to go to find out about this are immensely, immensely appreciated!

Click to expand...

I maybe missing something here, if so, I apologize, but since you're entering a number in A3, why can't you just enter that number in either C3, D3 or E3? And if you do want formulae, a simple IF should do.

In C3,

=IF(B3="Food",A3,"")

Then in D3, same formula, but change Food to Rent etc.

Hi Brian, no, you didn't miss anything, and you're right--I could just type it in, and I've been doing just that for about two years. But my finance sheet has evolved so that it has about 15 different categories, and going all the way across to enter them into the appropriate column has gotten tiresome. I figured it would be easier to have a drop down list that would automatically copy the value to the correct category. It's just more nifty that way.

Thank you also for the formula suggestion! I will play with your's and jindon's until I understand them. They seem to be working very well so far!


Hey, I do have another question for anyone who could answer this...is there some way to also make the color of the original cell(s) dependent on the selection of the drop-down selection?

Example: Selecting "Food" from my original example not only copies the value 12 into C3, but also turns the font of the original value 12 in A3 to blue.

Is that possible?

Hi,

Yes it is!

If the condition is upto 3, then you can do it by conditional formatting.

If the conditions exceed 3 then vba solution.

rgds,
jindon

So...hmm. Since I have more than three color categories I would want to change the font to (since I have more categories than Food, Rent, and School), conditional formatting wouldn't work, would it? If it would, how might I write that formula (or where can I learn about how to write these formulas, anyway)?

If I would need to go to the VB solution...how would I begin to approach that? Is there an online reference source for learning VB or a database for it that anyone could recommend? Programmer I am not...but I could give it a try.

If you explain the conditions or provide small sample,
I can write the code for you with the comments so that
you can arrange them later.

rgds,
jindon

Thanks jindon. As I look at my budding spread sheet, there's three cases where I can think of where I'd want more than three conditions. Your input on any one of these (or more!) is greatly appreciated:


Case 1: In D39 I record a numerical value (the cost of an item). In E39 I have a drop down menu that has categories of payment type (cash, ATM, credit card 1, credit card 2, etc.).

Now H39 is a column called "withdrawal" that represents a withdrawal from my checking account. I'd want it to list the value of D39 as a negative if and only if E39 says any one of these six selections: "ATM Withdrawal," "Debit," "Check," "CC Pay 1," "CC Pay 2," and "CC Pay 3."

So for example: I enter "25" in column D39 and select "Check" in E39. I'd like for H39 to then display the value "-25."


Case 2: I don't know if this one is possible: I'd like a cell to total up values from other cells depending on what a drop-down list in the adjacent cells to those values says. So if the drop down list is "Debit," "Check," "Credit Card 1," or "Credit Card 2" the adjacent value is included in the total, but if it says "ATM Withdrawal" or "CC Pay 1" the value is not included in the total.

For example: In D39 I enter "25" and in E39 I select "Debit." In D40 I enter "60" and in E40 I select "ATM Withdrawal." G41 (the cell with the conditional formatting or VBA, I guess?) totals up the value of the two cells as "25" and not as 85 because of the selections in the adjacent E cells.


Case 3: Depending on selections in drop down lists in two separate cells, the font color changes in a third cell.

For example: The font of cell B39 changes to red if I select "Eat In" in cell G39, blue if I select "Eat Out" in cell G39, purple if I select "Debit" in cell E39, and green if I select "Check" in E39. The selection in G39 has priority if selections overlap.


Cases 1 and 2 are of the most interest to me. I appreciate any coding suggestions or tips you can give me as to how approach this, jindon. I think this sheet is really going to simplify my life and make it easier to keep track of my expenses (if I ever get it working ).

Hi

All can be done for sure.

What is the Heading row?