Cell calculation - Text query ( Add / Subtract depending on text )

I think i have attached the document, well I've followed you instructions blindalley.

Look,apologies for the mistakes, and the lamely written
thread title, I'm a Firefighter, I'm British and I am very
new to this so any help is much appreciated, even if you yell.

Now if the attachment has uploaded correctly,
I have been trying for 2 weeks to put a formula
in the cells in column E headed Balance that can
distinguish between Accrued or Taken in column D.
If column D shows taken then balance will minus
column C hours, if column D shows accrued then cells in
column E will add the hours. The balance column need to be a running
total as well so the addition or subtraction from balance needs to be from
the cell above.

No apologies required, why aren't you watching football, my team is Burnley btw and I still watch the games :-)

Take a look at the attachment, it does what you are asking for but I suspect there is going to be a little more involved - ask away if there is.

BlindAlley,

Burnley fan, great, I think they had a draw against Hull today :-). I'm at bloody work today. I'm not sure
why I'm trying so hard with this spreadsheet, think its just become a challenge. We have no internal support
for excel so your basically on your own.

Thanks for the help, I've tried to play around with the formula you provided and it almost does what I
was hoping for. I have attached a second example with a bit more detail about what I would like to happen.

I may be asking a little to much from excel at the minute and I am a little naive, especially as its took me
all morning to get a cell to change colour when it exceeds a specified value, you get the idea of my level
of competence with this.

If my explanation is lacking clarity or my expectations are to high please let me know and I will just get staff
to input the balance changes manually.

Your help is very much appreciated.

Dan

Mr 999

Please change your thread title as per the moderators request. I can not help you until that has been done.

Ok I've tried to edit the post title to something more appropriate guys.

OK, I will take a look at this for you.

Your 2nd example gives me a better idea of what you are looking for.

You have 1 page per firefighter that pulls data from 1 sheet for the 'accrued hours' sheet. Formulas are needed to calculate the hrs if taken or not, leaving a balance that transfers to a 3rd sheet.

Thank you blind alley,

Yes that's correct mate, every firefighter has an individual sheet. The accrued or taken
hours are input manually by the officer in charge on the particular day. So that particular data isn't pulled from another sheet, just inputted on an ad-hoc basis.

And yes the balance is then transferred onto another sheet which is an overview that shows
each firefighters outstanding balance which can be viewed easily by managers at the
station, if the hours rise above 20 the cell in the overview then turns red and staff
can then be prompted to take some of the hours back.

If the whole spreadsheet will be of more help I will send in the morning as I am back
on duty.

Thank you very much for your help.

Dan

BlindAlley,

That second example is almost perfect mate. The only thing is that the taken hours don't deduct from
the balance.

In the example the actual balance should read 3:00 as this particular firefighter has accrued a total of 6
hours and then used (Taken) 3 hours.

I've tried to have a play myself but with absolutely no joy.

Hope the beers went down well ;-))

Dan

Morning Mr 999,


Beers went down well, coincidentally I ended up having a few beers at my neighbours place who is a Mr 911 Firefighter Captain

In the example I posted yesterday I took your suggestion to just have the balance at the bottom of the page, the total hours added up to 9 with 3 being taken leaving the balance of 6 at the bottom.
I have added another calc above the balance to show the total accrued hours.

See how this goes, and if you want me to take a look at the main file to see if any improvements can be made let me know.

Steve

BlindAlley,

I knew a gentleman in my service that transferred to a department in Canada, not sure where to
be fair but I think he may be a captain.

Right, I see where you are getting the numbers from but im not sure ive explained myself fully.

The "hours" column is individual hours allocated for either the incident or the stand-by.
If those hours are for an incident they are accrued, banked by the firefighter so they can use
them when they wish for time off. If the hours are allocated as stand-by they are taken.

In the example this firefighter has accrued (banked) a total of 6 hours, and out of those 6 hours he
has taken 3 hours back for time off. The balance in this case for this example to work needs to show 3 hours.

I dread to think how frustrating this may be for you so I will apologise and i promise this will be my last message :-)

No problem, I'm just chilling at home today.

I have adjusted the formulas to reflect the comments in your last post.

BlindAlley,

That is perfect, one last thing, can this same calculation be done with removing column E "Balance". I
can see you use it to calculate the balance at the bottom but it would look a lot better if the column,
could be removed now.

If not, or it complicates things tooo much then its fine I can live with it and i'll publish it the way it is,
i'll just put a different heading.

If it cant be done I will be happy with what ive got and I am uber gratefull for the help and I am sure
I will be one this forum much more in future.

Once ive formatted the complete sheet with the new forula I will share a copy of the complete workbook,
see what you think of my handy work...haha....or yours if im honest.

Job jobbed...column E removed.

Glad to see we finally got it solved, again, ask away if you need any more help.

BlindAlley,

That's great, I cant find the updated attachment though, did you attach it mate??

Originally Posted by Glorioso999

BlindAlley,

That's great, I cant find the updated attachment though, did you attach it mate??

Oooop,s I told ya I was out for a few beers :-)

I extra column removed and now showing the (hopefully) correct balance at the bottom.