Saltysteve
New Member
- Joined
- Jul 23, 2014
- Messages
- 36
- Office Version
- 365
- Platform
- Windows
Hi,
Any help with the following will be most appreciated.
In Sheet 2 I have the following formula starting at H5 down to H85. The formula counts how many inspections an inspector does between specified start and end dates.
=COUNTIFS(SHEET1!$E:$E,$E5,SHEET1!$A:$A,">="&IF($A5="",$G$2,IF($A5>$G$2,$A5,$G$2)),SHEET1!$A:$A,"<="&IF($B5="",$H$2, IF($B5<$H$2,$B5,$H$2)),SHEET1!$D:$D,"="&H$3)+COUNTIFS(SHEET1!$E:$E,$F5,SHEET1!$A:$A, ">="&IF($A5="",$G$2,IF($A5>$G$2,$A5,$G$2)),SHEET1!$A:$A,"<="&IF($B5="",$H$2,IF($B5<$H$2,$B5,$H$2)),SHEET1!$D:$D,"="&H$3)
Sheet 1 cells (raw data) are as follows: A – Dates, D – Inspection codes (4 different codes), E – Email addresses
Sheet 2 cells are as follows: E5: E85 – inspector’s primary email addresses (different inspector in each cell, also needs to be noted that the list needs to be able to grow), F5:F85 – inspector’s secondary email address, G2 – start date, H2 - end date, H3 – Inspection code
The same formula is actually in ranges H5:K85, AB5:AE85 and AP5:AS85. The other columns in each range covers the other inspection codes and the dates in each range change. ie – current month, previous month, year to date
This is the formula in AR85 for some clarity:
=COUNTIFS(SHEET1!$E:$E,$E85,SHEET1!$A:$A,">="&IF($A85="",$AO$2,IF($A85>$AO$2,$A85,$AO$2)),SHEET1!$A:$A,"<="&IF($B85="",$AP$2,IF($B85<$AP$2,$B85,$AP$2)),SHEET1!$D:$D,"="&AR$3)+COUNTIFS(SHEET1!$E:$E,$F85,SHEET1!$A:$A,">="&IF($A85="",$AO$2,IF($A85>$AO$2,$A85,$AO$2)),SHEET1!$A:$A,"<="&IF($B85="",$AP$2,IF($B85<$AP$2,$B85,$AP$2)),SHEET1!$D:$D,"="&AR$3)
With the raw data that gets updated regularly and all of these formulas (there are actually many many more), i'm sure you could appreciate how large the workbook is and how it tests my poor computers power, needless to say, it is very slow to recalculate.
I would like to convert this all to VBA to get rid of all of the formulas and possibly have it refer to another workbook for the raw data. Although if that would slow the execution of the code too much, I can continue to pull it into this workbook.
Any help with the following will be most appreciated.
In Sheet 2 I have the following formula starting at H5 down to H85. The formula counts how many inspections an inspector does between specified start and end dates.
=COUNTIFS(SHEET1!$E:$E,$E5,SHEET1!$A:$A,">="&IF($A5="",$G$2,IF($A5>$G$2,$A5,$G$2)),SHEET1!$A:$A,"<="&IF($B5="",$H$2, IF($B5<$H$2,$B5,$H$2)),SHEET1!$D:$D,"="&H$3)+COUNTIFS(SHEET1!$E:$E,$F5,SHEET1!$A:$A, ">="&IF($A5="",$G$2,IF($A5>$G$2,$A5,$G$2)),SHEET1!$A:$A,"<="&IF($B5="",$H$2,IF($B5<$H$2,$B5,$H$2)),SHEET1!$D:$D,"="&H$3)
Sheet 1 cells (raw data) are as follows: A – Dates, D – Inspection codes (4 different codes), E – Email addresses
Sheet 2 cells are as follows: E5: E85 – inspector’s primary email addresses (different inspector in each cell, also needs to be noted that the list needs to be able to grow), F5:F85 – inspector’s secondary email address, G2 – start date, H2 - end date, H3 – Inspection code
The same formula is actually in ranges H5:K85, AB5:AE85 and AP5:AS85. The other columns in each range covers the other inspection codes and the dates in each range change. ie – current month, previous month, year to date
This is the formula in AR85 for some clarity:
=COUNTIFS(SHEET1!$E:$E,$E85,SHEET1!$A:$A,">="&IF($A85="",$AO$2,IF($A85>$AO$2,$A85,$AO$2)),SHEET1!$A:$A,"<="&IF($B85="",$AP$2,IF($B85<$AP$2,$B85,$AP$2)),SHEET1!$D:$D,"="&AR$3)+COUNTIFS(SHEET1!$E:$E,$F85,SHEET1!$A:$A,">="&IF($A85="",$AO$2,IF($A85>$AO$2,$A85,$AO$2)),SHEET1!$A:$A,"<="&IF($B85="",$AP$2,IF($B85<$AP$2,$B85,$AP$2)),SHEET1!$D:$D,"="&AR$3)
With the raw data that gets updated regularly and all of these formulas (there are actually many many more), i'm sure you could appreciate how large the workbook is and how it tests my poor computers power, needless to say, it is very slow to recalculate.
I would like to convert this all to VBA to get rid of all of the formulas and possibly have it refer to another workbook for the raw data. Although if that would slow the execution of the code too much, I can continue to pull it into this workbook.