Add.Subtract toggled macro

[HP RodNuclear]

My company produces results from certain departments’ exposure for a particular day, the day after. Each department has a planned exposure amount, but then they have what we call “Emergent” activities, in which its unplanned work that is accounted into their total for the week. However, I was wanting to make a macro that would toggle subtracting the emergent work from the their total exposure i.e. it would subtract the emergent work out for a particular day, but add it back in per the user choice.

So what I need this macro to do specifically in regards to the dummy workbook using 1 department as an example (OPS) is:

On sheet 3, the code would find the department name(s) listed in row 2 and match it up with the work group name listed on sheet 2 in column C. It also would then look at the date in column A on sheet 2 and the corresponding value listed in column D on sheet 2. Then if the dates in column A on sheet 2 match the given date on sheet 3 in column D, it would subtract the value listed in Column D on sheet 2 from the value listed in sheet 3 column E for that PARTICULAR date BUT I would like for it to be toggled to subtract the value or add it back in, probably via 2 buttons on Sheet 3 called “Emergent On” and “Emergent Off”.

As an example to make it even more clear, for today, May 4th, looking at Sheet 3, the OPS department has .401 as total exposure, which includes emergent and planned work. If you go to sheet 2, for today, May 4th, OPS has 2 emergent activities that are both .008, for a total of .016. So if this macro worked, via the user clicking “Emergent Off” it would subtract .008 from .401 two times to equal .385 and that value would be seen in cell E6. But if I wanted to add that value back in, the user could click “Emergent On” and it would reverse the process and add the two activity values listed in sheet 2 column D back to the value listed in cell E6. Otherwise, if the current date is not listed for a particular department in sheet 2, it leaves the values listed on sheet 3 column E alone.

It seems like an impossible task but if someone is willing to help me out, that would be great. I also am learning this as well so if whomever decides to help me out is willing to teach pretty much a novice, who can learn quickly, that will be great too.

And sorry about bolding so much stuff, but i wanted to make sure everything was super clear.

HP RodNuclear

[realniceguy5000]

Hi,

Not sure I understand everything you want to happen but here is a test book to try and get you going. Press the button a couple times and see how the data looks to you. Let me know should you need additional changes or maybe I am completely off base.

Good Luck, Mike

[tigeravatar]

HP RodNuclear,

I have attached a modified version of your workbook that should accomplish what you're looking for. Let me know if you have any questions

~tigeravatar

[HP RodNuclear]

TigerAvatar, the code worked GREAT!!! I even tested it to see if it would update up to the current date and it does! Thanks. Now, I just hope I can transpose it correctly for the real workbook heh. But great job man...I'm going to go through the notes you wrote (which also help out greatly) to see if I can understand it and hopefully will make it easier for me to convert it to the real workbook. Thanks so much!

HP RodNuclear

[HP RodNuclear]

RealNiceGuy, your code worked as well, however, I chose tigeravatar's code b/c he had the button on sheet 3, which is where it is needed instead of sheet 2, BUT I do like how your button changes color when you press it...I think I am going to fuse the two codes together to get that type of effect. Thank you as well! (Feel sorry for leaving you out initially... )

HP RodNuclear