Newbie help required, you lovely people

[Ricoventry]

Hello there,

I find myself at this, my first Excel forum, for I find myself with nowhere else to turn. Any help offered will incredibly gratefully received.

I'm working on a holiday tracker, and it's pretty evolved as it stands. The issue I have two sets of staff on the same page but treated differently; namely full-time staff and part-time staff. A code in a calendar denotes a value that I manipulate elsewhere.

The code in this instance is 'S'. If I enter an 'S' into the corresponding day for a full time individual, I will track that they have been off sick for 7 hours (their normal working day).

If that staff member is part-time, I will put S followed by the number of hours they've been off sick for. So, if a part-time member of staff is schedule to be in for 4 hours but phones in sick, the code will be S4.

Now, the code I'm using to extract the numerical data after the S is this:

{=SUM(IF(LEFT(L$5:L$200)<>"S",0,--MID(L$5:L$200,FIND("",L$5:L$200)+1,LEN(L$5:L$200))))}

Whilst this seems to work correctly, I've noticed that if a full time member of staff in the same range is off sick, then when I put a simple 'S' in that box, I am met with a value error. I have attempted the following, but to no avail:



{=SUM(IF(LEFT(I$5:I$120)<>"S",AND(LEFT(I$50:I$198,2)<>"",--MID(I$50:I$198,FIND("",I$50:I$198)+1,LEN(I$50:I$198)))))}


Now, owing to the set requirements I have the following restrictions:

• I am not permitted to use VBA
• I cannot simply put S7 for the full time members of staff.
• I'm using a combination of Excel 2010 (home) and Excel 2013 (work)

As I say, I'm really grateful to whoever's taken the time to read through this.

Thanks,

Ricoventry

 

[GerryZ]

Hello!
I like this question but I don't understand all of the issue It was nice to see a spreadsheet sample instead to use 1000s of words and formula
Can you post a screenshot with just few rows and colunms with the expected outcome so we can understand better?
help us to help you
Thank you in advance!

 

[Ricoventry]

Thanks for getting back so quickly GerryZ.

The forum seems to need me to host the images so that I can link to the URL - I'll try to sort that and get back to you as soon as possible.

Thanks again,

Ricoventry

 

[Snakehips]

Ricoventry,

Welcome to MrExcel.

If as I understand, you wish S to be included in the sum as value 7 ?????

Then try

=SUM(IF(L$5:L$200="S",7,IF(LEFT(L$5:L$200)<>"S",0,--MID(L$5:L$200,FIND("",L$5:L$200)+1,LEN(L$5:L$200)))))

Confirmed with Ctrl + Shift + Enter

Also see links in my signature notes if you wish to post a snapshot.

Hope that helps.

 

[Ricoventry]

Wow! That's it - that has solved literally days of overwhelming panic!!

Thanks very much for your help both - and Snakehips, as you might be able to tell from my moniker, I'm a fellow Coventrian - this was fated.

Brilliant

 

[Snakehips]

You are welcome.

Always good to keep it in the family!