Excel Gurus: How would I...

[bovinda]

Here's what I would like to do: Selecting an option from a drop-down list causes the value entered in one cell to also appear in another cell (that is predetermined by the selection in the drop down list).

Example: I enter 12 into cell A3. In B3 is a drop down list containing "Food," "Rent," and "School" as options. If I select "Food" then whatever value is in A3 (in this case, the value "12") is also copied into C3. If I select "Rent," then A3's value is copied into D3 instead, or to E3 if I select "School."

Is this possible at all? I've heard that Visual Basic can be used to enable more functionality to things like drop-down lists, but don't know if that's true, or how I would go about doing that. I don't know the first thing about scripting or programming of any sort.

Any suggestions as to how I could do this or where to go to find out about this are immensely appreciated! :beer:

 

[MrBond]

You might have to use a macro for something that complicated - it shouldn't be too difficult with a macro.

VB in Excel is fairly easy, even if you didn't have programming experience (but it helps). I'm at home visiting my parents for the weekend, but if you don't have a solution by the time I get back to my place tomorrow, I'll see what I can whip up in excel. I'm on my laptop now and it's not ideal for writing macros for me.

 

[PowerEngineer]

007 is right

You'll want to use the "assign macro" option on the combo-box to run a VB macro that will use the index in the combo-box's "cell link" to determine which column to move the value to. Look at the Range and Cell methods in VB.

Good Luck

 

[bovinda]

Thanks guys. I will try to see what I can come up with and I'll post my results tomorrow morning sometime. MrBond, I really appreciate your offer. I'll see what I can learn tonight about VB.

 

[NeoV]

no VB needed

use conditional formatting to make your drop down list...use a column off to the far right, or a hidden column so the drop down list isn't visible..also, make sure you include one blank cell in the range of options for your list, that way you can have a blank selection as well as the three choices you want.

In C3, you have an if statement that reads =if(b3="Food",a3,"")
then in D3, similarly you have =if(b3="Rent",a3,"")
and in E3, =if(b3="School",a3,"")

 

[bovinda]

Thanks NeoV, that was exactly what I was looking for--it works perfectly! And I didn't even have a chance to touch VB yet...

I have another question though, now that it's getting so nifty. Is it possible to make the drop-down selection also effect a color change in the font of the original cell?

Example: From my original example, if I select "Food," then not only the value "12" would be copied to C3, but the font of A3 would also be changed to blue.

Is that possible through conditional formatting? Or would something like that require VB?

Thanks guys! :beer:

 

[NeoV]

conditional formating works for that as well

 

[bovinda]

Three last questions then. Am I limited to choosing three colors, since I read somewhere that conditional formatting is limited to 3 conditions? If so, could a VB macro do something better?

And if I'm not limited to three colors, um, how would I write the formula for that (or where would I find out)? I have no knack for this formula writing thing yet...

 

[bovinda]

Bump!

 

[NeoV]

you are correct, you are limited to three colors for a given cell using conditional formatting - generally used in a scenario where it's one color if the amount is equal to a certain number, another color if it is greater than that number, and the third color if it is less than..

yes, with VB you could technically have the entire palatte of colors available, but you would have to run the VB script via a macro after your data input, where as conditional formatting applies the rules/colors right away.

You sure you need more than 3 colors?

 

[bovinda]

Yeah, I would need more than three, but it's really not that important probably. However, there are two other cases where I would definitely need more than three conditions, I think, and they're a little more complicated. I'll list the other cases in case you or anyone else might have some input about them.

So for VB scripts, you enter the data, and then you have to click somewhere to run the script? Is it something you can copy and paste from one cell to another like conditional formatting?

Anyway, here are the other cases where I'm pretty sure I would need VB scripts. Any input as to how to approach this, or if it's even doable is welcome...like how much research will it take to learn how to do this--is it doable for a beginner?


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.


How much work do you guys think is involved in this?

 

[NeoV]

not much work at all

Case 1 - how many different items would be in your drop down list? You could just use a 'nestled' if statement to accomplish this - for example, =if(h39="ATM Withdrawal", -d39,if(h39="Debit", -d39, if(h39="Debit", -d39,if(.......as many times as you need it...it may be faster to list the drop down options where you wouldn't want the negative, depending on the size of your drop down list.

Case 2 is very easy, but I'm not sure how your form would look - you could go several different routes, using filters or a pivot table, or even the =countif(range,criteria) function.

email me a copy of the spreadsheet when you are ready

 

[bovinda]

NeoV, thank you so much for the offer. I will organize my thoughts better about it and e-mail you something in a day or two. If it's not too much trouble, any suggestions/help is immensely appreciated, and if you decide you don't have the time either, no worries. Thanks man! I will be in touch shortly.

Gratefully,
Jeff

 

[TSDible]

You can only have 3 different cell formats with conditional formatting, but you can definately have more conditions than 3.

This can be done by using the formula function of conditional formatting along with an "or" statement.

I have examples if you need them.