A2H GO
Member
Registered: 14th Sep 04
Location: Stoke
User status: Offline
|
Ive done the template.
It two worksheets, a logging sheet and a summary sheet.
It relys on staff entering a prefix followed by the number of hours. eg. For 7 hours holiday they enter H7.
Where I'm stuck is that I don't know how to total up the different prefixes on the summary sheet. Not sure whether it can be done using formula or if code is needed...
Link to file is here:
http://www.filetolink.com/8244aa2c
 
|
A2H GO
Member
Registered: 14th Sep 04
Location: Stoke
User status: Offline
|
I've done a simple table as an example of what i need.
In the Total hol column I need a forumla that will look to see if theres a H in the cell, if so it will add the numbers next to it.
So in this example the total hol will equal 9.
|
ashleh
Member
Registered: 23rd Dec 08
Location: Nottingham
User status: Offline
|
This is in Excel right? Hows your programming skills  .
|
A2H GO
Member
Registered: 14th Sep 04
Location: Stoke
User status: Offline
|
Not good, hence using formula
I'm really close actually:
=SUMPRODUCT((LEFT(B5:G5,1)="H")*(RIGHT(B5:G5,LEN(B5:G5)-1)))
This returns the total of the numbers in the cell if it contains a H, ie. It returns 9 in the example above.
The only problem I'm having is that if one of the cells is blank it returns #VALUE!
|
ashleh
Member
Registered: 23rd Dec 08
Location: Nottingham
User status: Offline
|
If statement? I was thinking VBA but you might be able to do it your way with a little tweaking.
|
Dom
Member
Registered: 13th Sep 03
User status: Offline
|
Have a look at the ISBlank funtion or ISError, one of those should help you. Although doing it in VBA would be a lot easier (just the case of looping through the range with a simple IF Statement).
|
A2H GO
Member
Registered: 14th Sep 04
Location: Stoke
User status: Offline
|
Cheers Dom.
I had a go with VBA and failed miserably. 
|
A2H GO
Member
Registered: 14th Sep 04
Location: Stoke
User status: Offline
|
Done it!
=SUM(IF(LEFT(C17:G17,1)="H",--RIGHT(C17:G17,LEN(C17:G17)-1)))
|
ashleh
Member
Registered: 23rd Dec 08
Location: Nottingham
User status: Offline
|
 I'm guessing you spent about 2 hours googling? That's what I usually do .
|
Dom
Member
Registered: 13th Sep 03
User status: Offline
|
quote:
Originally posted by A2H GO
Cheers Dom.
I had a go with VBA and failed miserably.
code:
TotalHoliday = 0
For Each LoopCell In Worksheets("Sheet1").Range("B5:G5").Cells
If LoopCell.value <> "" And Mid(LoopCell.value,1,1) = "H" Then TotalHoliday = TotalHoliday + Mid(LoopCell.value,2,1)
Next
Range("G6").Value = TotalHoliday
Something like that although that only does one row so you'd need to put the above in another loop that goes through all the rows (obviously updating the row number in the inner-loop).
|
A2H GO
Member
Registered: 14th Sep 04
Location: Stoke
User status: Offline
|
quote:
Originally posted by ashleh
I'm guessing you spent about 2 hours googling? That's what I usually do .
Yes
|
A2H GO
Member
Registered: 14th Sep 04
Location: Stoke
User status: Offline
|
quote:
Originally posted by Dom
quote:
Originally posted by A2H GO
Cheers Dom.
I had a go with VBA and failed miserably.
code:
TotalHoliday = 0
For Each LoopCell In Worksheets("Sheet1").Range("B5:G5").Cells
If LoopCell.value <> "" And Mid(LoopCell.value,1,1) = "H" Then TotalHoliday = TotalHoliday + Mid(LoopCell.value,2,1)
Next
Range("G6").Value = TotalHoliday
Something like that although that only does one row so you'd need to put the above in another loop that goes through all the rows (obviously updating the row number in the inner-loop).
Nice one, I'll have a play about with this on Monday!
|
