multiple cell value by .85 under certain condition.

[Aces and 8s]

Hello,

I am an excel novice, I can make my way around and use some simple formulas, but not versed with the more advanced ones.

That said, I am in a bind and already tried searching google, perhaps I can't find any results because I am not wording it right. I apologize in advance for not using the search function here, again, in a quick bind.

I need a formula for:

If cell C2 has the letter "N", then multiply the what ever is entered into cell E2 by 0.85.

I hope I explained this correctly, basically I want whatever I enter into cell E2 to be multiplied by 0.85 automatically if the value "N" has been entered into cell C2.

I also want to make this formula work for cells C3 and E3, C4 and E4, C5 and E5, and so on and so forth.

Oh, the kicker, I don't want to create another cell, I want the formula to work based on no other data than what is enter in "C2" and what get's input into "E2". I know I can do this if I create a third cell, but I am hoping there is a way to do this with only the two cells already created.

[Sam Capricci]

try =if(c2="N",e2*.85,"")
but you'll have to have a cell to enter this into.

[Sam Capricci]

BTW, you probably ought to change the title of your post to something more descriptive rather than help w/formula.

[Aces and 8s]

Thanks, but that did not work and requires entering data in a third cell, trying to only use the two. Not even sure this is possible.

Maybe with a conditional formatting? Or Visual Basic?

[Fotis1991]

Welcome to the forum

Your post title should accurately and concisely describe your problem, not your anticipated solution.

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

[Aces and 8s]

Welcome to the forum

Your post title should accurately and concisely describe your problem, not your anticipated solution.

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

That's all fine and dandy, if I knew a way to accurately describe what I want to do, I would have done that. Now my thread title looks silly.

[bmxfreedom]

Oh, the kicker, I don't want to create another cell, I want the formula to work based on no other data than what is enter in "C2" and what get's input into "E2". I know I can do this if I create a third cell, but I am hoping there is a way to do this with only the two cells already created.

If you don't want to use a 3rd cell, this can only be done with VBA. I would use the worksheet change event to look at changes being made in column C, and if those changes are "N", then E & target.row = E & Target.row *.85 like this:

Please Login or Register  to view this content.

To apply this code, right click the sheet, choose "view code" and paste it there.

[bmxfreedom]

If cell C2 has the letter "N"

Aslo, by "has" do you mean "is equal to" or "contains"? If you mean to look for the value "N" within the value in the cells in column C, then the code would need to change.

[Aces and 8s]

Basically the two options would be Y (yes) and N (no). So any time N is in a "C" cell, I want the corresponding "E" cell to minus 15% from what I enter into it.

[Fotis1991]

That's all fine and dandy, if I knew a way to accurately describe what I want to do, I would have done that. Now my thread title looks silly.

Now does not.. So simple.

[bmxfreedom]

Basically the two options would be Y (yes) and N (no). So any time N is in a "C" cell, I want the corresponding "E" cell to minus 15% from what I enter into it.

The code I wrote should do exactly what you're asking. Try it out.

[Aces and 8s]

Gave it a shot, I entered the VBA and then put N in a "C" cell and entered 100 into an "E" cell, and it stayed at 100 instead of recalculating to 85.

[Aces and 8s]

excellproj.JPG

Maybe this screen shot of the sheet I am using will help?

[bmxfreedom]

Hahaha! It's quite possibly because I misspelled "range" in my code...

Try:

Please Login or Register  to view this content.

Keep in mind that this code will only run if you change a value in column C. If you want it to run with changes in other columns, you'll need to expand the if statement to Target.column >= x And Target.Column <= x.

In the scenario exactly as you described it, where you enter an N into a C cell, then a 100 into an E cell, the code would have already tried to run before the 100 was entered, and would have resulted in a 0 value. However, my misspelling would have resulted in an error, which should have popped up an error message if this code was truly placed in the sheet code. There is also the possibility that you have events disabled, which would make this code not run as well.

You can get around the use of change event code by using a macro that you run manually after making your changes, but that gets more complicated and requires a loop. I would recommend sticking with the event code; just change my misspelled "ranage" to "range"... Sorry about that.

[Aces and 8s]

I noticed the spelling error and changed it before entering, new entries or older entries don't, change.

[bmxfreedom]

It works perfectly in a test workbook I built. Are there any errors popping up? Here's a test to see if the code is actually running (to see if events are enabled). In front of the if statement, place: MsgBox "hello" like this:

Please Login or Register  to view this content.

This will cause a message box to pop up that says Hello every time a change occurs. If that doesn't happen, then the code is not running, in which case, you'll need to write a macro in a standard module and run it to turn events back on, like this:

Please Login or Register  to view this content.

[bmxfreedom]

new entries or older entries don't, change

Are you saying you want to update all values, not just the value on the row being changed?

[Aces and 8s]

Nope, still not working. I don't know what's wrong, works for you, so I believe you. I enabled macros and did the test you suggested, nothing.

I give up, thanks for your help anyways.

[bmxfreedom]

Two things; 1, make sure the code is in the correct worksheet object, not a standard module. (Do this by right clicking on the sheet, choosing "view code", and pasting in the sheet code module that pops up. 2, Try changing it to the following:

Please Login or Register  to view this content.

This will cause the code to trigger on any row where something changes if the cell that is changing is between columns C and E.

[popipipo]

'N' is case sensitive

[Aces and 8s]

Two things; 1, make sure the code is in the correct worksheet object, not a standard module. (Do this by right clicking on the sheet, choosing "view code", and pasting in the sheet code module that pops up. 2, Try changing it to the following:

Please Login or Register  to view this content.

This will cause the code to trigger on any row where something changes if the cell that is changing is between columns C and E.

It seems to be doing something now, with that revised code. When N is entered into a "C" cell (ex C1) it turns E1 from blank, to a value of "0.00". When I enter 100 into E1 however, it changes it from my entered 100 back to 0.00 when I am done editing the cell.

[bmxfreedom]

I forgot a key piece of that puzzle. Here, try this:

Please Login or Register  to view this content.

[bmxfreedom]

Here's an even better version. It only causes changes when they are made in either column C or column E, and it takes away the case sensitivity of "N".

Please Login or Register  to view this content.

[Aces and 8s]

SUCCESS!!!!!!!!! Thanks a bunch!