Solved MS Excel Combining Complex Formulas

April 15, 2013 at 17:47:55
Specs: Windows 8
I have two separate issues:

Issue 1:

I have a list of employee names in column A.
I have their departments listed in column B.
I have their roles listed in column C.
I then have 52 weeks spanning from column D on wards.
The purpose of this sheet is to provide a forecast of competent employees for each week.
I mark a "C" for the employee that is forecast to become competent that week.

I am using the following formula to calculate the competencies forecast per week. This formula was working great.


$E$39 is calculating all of the employees already competent on another sheet. I must maintain the two separate sheets, as they both have a lot of individual requirements.

The problem I have now is, if we are currently in Week 1, and I mark an employee as being foreseen to become competent in Week 3, this calculates fine, but when I actually get to Week 3 and I update the second sheet of employees already competent, it is doubling the total of competent employees by using this formula.

To counteract this, I inserted a hidden row under the cell with the COUNTIFS formula in it and put this formula in the cell =IF($E$39>=Q$84, Q$84,$E$39)-Q$84. Q84 has the total of competent employees for that week from the other sheet.

This worked great, so I have been just summing the two formulated cells to give the accurate total of competent employees for that week, including all currently competent employees. The purpose of this total is to know the overall operational skilling per week, per department, per role, but not by employee.

So my question for issue 1 is: is there a way to combine both formulas, so I don't need to have hidden rows of formulas.

Issue 2:
In relation to the same sheets. If we mark the employee as being "C" in say Week 50. We would prefer not to have to mark them "C" for the remaining weeks. Meaning once they are competent, the records for their row cease as they are no longer in training to become competent, and are therefore just a value in the total operational skilling for that week.

My question for Issue 2 is: is there a way to have Issue 1's formula calculating that if a "C" is placed in say Week 50, it calculates the employee as being competent in the remaining weeks without us placing a "C" in the remaining weeks. We are using a dropdown to enter the "C" into the cells for each employee, as there are other options not relating to the mentioned formula that we need to select as well.

I thought maybe something to say that if a C is selected from the dropdown, it enters a C in the remaining cells per week, but as white text to it cannot be seen. That way it will show the week they become competent as being "c", and nothing on the remaining weeks because their training is complete. However, the operational skilling is still correct as it is caclulating all the white C's.

Please help! Thank you.

See More: MS Excel Combining Complex Formulas

Report •

April 16, 2013 at 07:04:05
✔ Best Answer
I haven't had a chance to work on Issue # 2, but for issue # 1, why not just SUM the 2 formulas in one cell?

Granted, I don't have an exact copy of your spreadsheet with all it's data, but a quick check of this formula produced the same answer as using the 2 separate formulas and then summing them.

=COUNTIFS(Department,"MELAMINE 3",Role,"IN-FEED OPERATOR",$D3:$D10,"C")
+$E$39+(IF($E$39>=Q$84, Q$84,$E$39)-Q$84)

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

April 16, 2013 at 10:22:59
For Issue # 2, I have a question:

Do you need to count all the hypothetical "white C's"?

In other words, if a C in entered in Week 3 and you want C for every week until the end of the year, there would be a total of 50 C's based on 52 week year. Is that "50" used some place as part of a calculation?

If so, you can calculate the number of C's without actually having them in the cells.

For example, if Week 1 starts in Column D, then Week 52 would be in Column BC.

Column D is Column 4
Column BC is Column 55

The COLUMN() function returns the column number in which the function resides.

Therefore, if you use this formula in Column D and drag it to Column BC, it will return the total number of weeks left in the year based on where you enter the first C:



In Column D, this would be the same thing as:

=IF(D5="C",56-4,"") so a C in D5 will result in 52.

In Column S, this would be the same as:

=IF(S5="C",56-19,"") so a C in S5 would return 37

Does that work for you?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

April 16, 2013 at 12:40:07
Hi DerbyDad03,

Thank you for your responses! I came to the same conclusion yesterday about Issue 1, so that's good, tick!

I see what you are saying about about Issue 2, and I think this will work. I will apply today and hopefully it resolves the issue.

You are a life saver! Thank you.

Report •

Related Solutions

April 16, 2013 at 13:06:24
Another option for Issue 2 is a macro that puts actual "White C's" in the remaining columns once a C is entered,

You can't do that with a formula in the same cell as the drop down so a macro or a formula in a different cell (like I suggested above) will be required.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

April 16, 2013 at 13:11:52
I think I like the macro idea better, I don't have too much experience with macros, but know the basics.

Can you let me know what the macro would be, and I will change the cell references to suit?

I will be so happy when this spreadsheet is done! :)

Report •

April 16, 2013 at 13:13:41
Just to clarify, I need a white C to appear in each cell for the remaining weeks, after the week has been marked as a C using the drop down.

Report •

April 16, 2013 at 13:54:50
This code should fill in the cells from the current column + 1 to Column BC with white C's.

It only checks for a C in Columns D:BB, because a C entered in BC (Week 52) should remain a visible C.

Note: With this version of the code, if you enter a C in the wrong cell, you will have to manually remove the white C's from the rest of the Row and reset the font color to whatever it was beforehand.

I should be able to fix it so that "mistakes" can be corrected by the code, but I don't have time right now.

Let me know if this works for you and I'll see about enhancing it later.

Private Sub Worksheet_Change(ByVal Target As Range)
'Determine if Changed cell is within D2:BB78
 If Not Intersect(Target, Range("D2:BB78")) Is Nothing Then
'Make sure only one cell has been changed
  If Target.Cells.Count = 1 Then
'Determine if cell has been changed to C
   If Target = "C" Then
'Disable events
    Application.EnableEvents = False
'Put C in current row out to Column BC
'Set Font color to "white"
       With Range(Cells(Target.Row, Target.Column + 1), Cells(Target.Row, "BC"))
        .Value = "C"
        .Font.ThemeColor = xlThemeColorDark1
       End With
     End If
'Enable events
    Application.EnableEvents = True
   End If
  End If
End Sub

The following piece of code is so that you can reset EnableEvents if the above code fails after setting EnableEvents to FALSE.

Sub ReEnableEvents()
  Application.EnableEvents = True
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

April 16, 2013 at 21:13:35
That worked perfectly! Thank you so much! Yay!

Report •

April 16, 2013 at 22:07:36
Damn! How do I change it so that I can use it for multiple sheets in one workbook? I have four sheets I need to use it on.

Report •

April 17, 2013 at 03:56:32
You need to put a copy of the code in each worksheet module.

A worksheet change event macro only monitors changes in the sheet in which it resides.

How are you planning on dealing with user errors, such as choosing a C in the wrong cell?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

April 21, 2013 at 21:44:56

Sorry for the delay, we just had to move interstate so I haven't had a chance to reply.

I will apply to all worksheets tomorrow and see how it goes. I've been given an extension on the spreadsheet presentation, which helps! I don't think I will need to deal with user errors yet.

With the 'C's that need to be white, how can I change the colour of the text. I need it to be light pink, as the cell is shaded light pink. That way they will be invisible.

Also, a formula that I thought was working correctly isn't, I need help!
I need a formula for the following scenario.

If the total 'C's in SHEET2 (split into weeks) is greater than the total 'C's in SHEET1 (competency records are kept by employee), the formula returns the total 'C''s in SHEET2. But if the total 'C's in SHEET 2 is less than SHEET 1, the formula returns the total 'c''s in SHEET 1. Lastly, if SHEET 2 is equal to SHEET 1, then subtract the difference from the totals.

I hope you can help! Thank you so much!

Report •

Ask Question