Help with formulas

[nicolam1005]

Hi

I have a formula, in a document which I did not create (a leave chart) which for some reason, takes the sickness off the leave totals, which I do not want so I want to exclude this from the leave formulas.  However, I do not understand the formula (I am a basic user) enough to be able to make the change I want.  Can anyone help me understand this formula below

The formulas is....

=SUMPRODUCT((OFFSET($A8,0,1,1,372)<>"")*(IF(OFFSET($A8,0,1,1,372)=$NW$13,0.5,IF(OFFSET($A8,0,1,1,372)=$NW$14,0.5,1))*(OFFSET($A$3,0,1,1,372))))

I have attached the document in question - if you type an S - it adds it to the leave totals.

Please help :) 

[#2]

@nicolam1005 

See the attached version.

[#3]

@Hans Vogelaar 

Thank you, you are a genius and it works on your document, but it will not allow me to copy it over to my document for some reason?

Also the H1 and H2 function adds 1 in the end boxes rather than a 0.5 or half.

Any ideas.  Sorry to ask more questions, but I sent the original document and not the one I had already adapted to the business

[#4]

@nicolam1005 

In the sample workbook, columns NT and NU simply count the number of cells with H1 and H2, respectively.

In columns NJ and NK, these count for 0.5.

So for example if there are three cells with H1 in row 8 (and no other cells with codes), cell NT8 will show 3, but cells NJ8 and NK8 will show 1.5.

It's impossible for me to know why it doesn't work in your own workbook without seeing (a copy of) it.

[#5]

@Hans Vogelaar 

Dear Hans

Apologies for the delay in responding - I couldn't get back to this.
I should have attached the document I actually use, which would make more sense.
I thought I would be able to copy over what you did to sort out the issue with the sickness days, but it does not seem to copy over. Any further suggestions. This is the actual chart I current use.
Kind regards
Nicola Morris

[#6]

@nicolam1005 

See the attached version. I had to save it under another name since your workbook opens read-only.

[#7]

Hans, you are a legend, how you did this I have no idea, I fumbled my way through for about 3 hours. Thank you for sorting it out.
Kind regards
Nicola

[#8]

@nicolam1005 

I copied the formulas from my previous attachment and adjusted them for the slight differences in layout (codes in column NW instead of NX, and H1/H2 in row 13/14 instead of 16/17).

[#9]

@Hans Vogelaar 

Hi Hans
You kindly helped me before with my Excel leave tracker and I now have another query.

I have added a CF code (carried forward) which is when a staff member is using up leave from the previous year, so I do not want it to add to the totals for the current year but I cannot figure out how to stop it, are you able to help.

Also, I would like the codes in column NW to come out to show with same formatting in the sheet, but they do not unless I manually change them. How do I adjust this (for example if you type in H1, M P, C it is not Bold or CF doesnt show colour)

I would appreciate your help if you have a moment.  I cannot see anywhere to attach the document again.

Kind regards
Nicola

[#10]

@nicolam1005 

Don't you see an area with the text "Drag and drop here or browse files to attach"?

If not, you can upload the workbook to for example Dropbox, OneDrive or Google Drive and post a link to the uploaded and shared file here.

[#11]

@Hans Vogelaar 

Found it - if you need a password please use Merlin123

[#12]

@nicolam1005 

See the attached version for the modified formulas.

I don't understand why you want to use conditional formatting in column NW.

[#13]

Thank you as always Hans.

I am not sure what you mean about conditional formatting in NW. It might be the way I explained it, How it looks in NW is how I want each code to look on the actual chart, Does that make more sense?

Nicola

[#14]

@nicolam1005 

Ah, OK.

You had a ton of conditional formatting rules; I tidied them. I hope it works the way you want now.

[#15]

You are a star Hans..... thank you for your help