Sum two cells only when a third cell is a specific charecter

**poser765** · 04-10-2009, 11:49 PM

Sorry about the title...I couldn't think of how else to word it.

Ok, i've got something that looks like this...

A1 has lesson "1-1" and B1 has "5" hours
A2 has lesson "1-2" and B2 has "10" hours

In cell A5 I would like to enter the lesson number, and B5 the time spent on that lesson. If I put spent 3 hours on lesson 1-2 I would like to "1-2" in cell A5 and 3 in cell B5 and then have 3 subtracted from cell B1.

Is that possible within the scope of Excell or am I in over my head?

Thanks.

**DonkeyOte** · 04-11-2009, 02:05 AM

How many times will you be looking to alter the values in B1/B2 etc...

Are you running 2008 Mac ? If you are you have no VBA functionality so will most likely need to adopt Iteration approach (Circular references) ... if you only want to change the values in B1/B2 once it's no big deal, more than that and it's open to error IMO

Suggestion: enable Iteration - set Max Iteration to 1 (Options)

Clear B1 & B2 of values

B1: =$B1-IF($A$5=$A1,$B$5,0)
copy to B2

To set up initial hours:

-- Set A5 to first lesson and enter -5
-- Clear A5 (delete contents)
-- Alter B5 to -10 and enter lesson number to B5 to 1-2 and enter -10
-- Clear A5:B5

Thereafter adjust the hours as required.

Note:
With Auto Calc on the iterative calcs will be performed with each calculation of XL so I would advise that once you have updated the cell of choice by altering B5 you should/must clear A5 (delete the contents), this way you avoid continuously counting the adjustment for whichever lesson is specified in A5.

I hope that makes sense.

If you only need to adjust once you can alter the formula applied to B1:B2 such that it only calculates once.

**poser765** · 04-11-2009, 09:31 AM

Sorry. i think I got so caught up trying to figure out how to descrbe what i was trying to do that i neglected to mention I am can use both office 03 and 08 on a windows machine.

That out of the way, each lesson is about 1 hour long, So B1 will be altered roughly five times and B2 circa 10.

Once I start defining some of the terms in your post I can probaly start seeing where you are going...just might take a minute or two.

You mentioned VB, is that more likely the direction I am going to have to go with this? Sort of a side project I hoped to impress the boss with. may have to just stick to bringing him his coffee

**DonkeyOte** · 04-14-2009, 05:28 AM

You can use VBA if you want to yes, given you have 2 cells to complete a button approach is probably best which when pressed updates the balance according to the lesson number.

That said the iteration approach should work but as I say is open to error if you don't remember to clear the lesson value from A5 once you've updated the hours...

"Swings and roundabouts" as we say here in the UK... both have their advantages / disadvantages.