I have two spreadsheets in the same workbook. Both spreadsheets a have a similar layout for the monthly columns portion. 12 columns with the "month" name for column headers and below each monthly header a numerical #. For the first 12 months 1 -12 entered under each month respectively. Following the 12th month, there is is a column with the header "Total" and the year, then comes another 12 columns with the monthly headers and 13 -24 entered under each month, then another column with "Total" and the year, and so forth for 5 years. Note: for each "Total" column there is no sequential monthly identifier number like the months have. This is the reason this formula is challenging...for me at least.
In the spreadsheet ('Bus Dev'!) where the formula is input, the formula looks above in its column to see the numerical value associated with the month (cell T13 in the formula below). For instance, if we are in the 2nd year under the April column, the numerical value is 16 (12 months for the 1st year plus 4 (April) for the 2nd year. Once I have this numerical value (16) I add it to another variable that uses a VLOOKUP function to determine if I need to add 2, 1, 0, -1, -2 months to this value. If the VLOOKUP function returned a -2, then the monthly numerical value is 14 (16 -2 ). I then can use this to look up the data on the other spreadsheet ('S-OEM'!) (that has a similar structure) using INDEX (MATCH) functions returning the column over from the left in which the data resides. This gets around the issue with the "totals" column since, if I'm in a cell next to or within 2 columns of the totals column, my calculation is impacted if I were to just OFFSET by the VLOOKUP value. By using INDEX (MATCH) it finds the correct column every time. But of course there has always got to be a wrench thrown in to make things more difficult...
Once I get to the correct column on the spreadsheet with the data, I'm using the SUMPRODUCT function to compare 4 columns of the same row (all the way down the spreadsheet) and if all 4 conditions are met (which only one row per column meets all 4 criteria) the formula gives me a value 57 rows below the trigger row (where all 4 criteria are met) and in the same column. I then use this SUMPRODUCT value to do other calculations back on the spreadsheet with the formula ('Bus Dev'!). Here is the formula that I'm using that finds the correct value on the data sheet (and it works):
=INDEX('S-OEM'!$H$17:'S-OEM'!$AY$1519,SUMPRODUCT(--('S-OEM'!$G$17:$G$1519="Headcount:")*--(OFFSET('S-OEM'!P$17:P$1519,0,(VLOOKUP('Bus Dev'!$C20,'Bus Dev'!$J$7:$N$10,5)-2))="")*--(OFFSET('S-OEM'!P$17:P$1519,0,(VLOOKUP('Bus Dev'!$C20,'Bus Dev'!$J$7:$N$10,5)-1))="")*--(OFFSET('S-OEM'!P$17:P$1519,0,(VLOOKUP('Bus Dev'!$C20,'Bus Dev'!$J$7:$N$10,5)))>1)*'S-OEM'!$A$17:$A$1519)+57,MATCH('Bus Dev'!T13+VLOOKUP('Bus Dev'!$C20,'Bus Dev'!$J$7:$N$10,5),'S-OEM'!$H$17:$AY$17,0))
The OFFSETs are checking to make sure there is no headcount 2 months prior and 1 month prior to headcount being > 1 in the column where my answer is found. So the problem comes in when I'm within 2 columns of the "totals" column (i.e. months Nov & Dec, and Jan & Feb), because when the OFFSET shifts over 2 columns there is no monthly numerical value under the "Total" column heading.
Below is the part of the formula that does the look up and finds the correct column on the data spreadsheet ('S-OEM'!):
MATCH('Bus Dev'!T13+VLOOKUP('Bus Dev'!C20,'Bus Dev'!$J$7:$N$10,5),'S-OEM'!$H$17:$AY$17,0)
Is there a way, to embed the MATCH function above into the three SUMPRODUCT arguments/arrays?
(I don't necessarily need to embed it in the "Headcount" argument since it is static and will be used in every formula as I copy it across).
Here is the first of the three arguments:
--(OFFSET('S-OEM'!P$17:P$1519,0,(VLOOKUP('Bus Dev'!$C20,'Bus Dev'!$J$7:$N$10,5)-2))="")
If I try replacing the: 'S-OEM'!P$17:P$1519 portion of formula with:
MATCH('Bus Dev'!T13+VLOOKUP('Bus Dev'!C20,'Bus Dev'!$J$7:$N$10,5),'S-OEM'!$H$17:$AY$17,0) I get an error message.
Any suggestions? Thank you very much!!
In the spreadsheet ('Bus Dev'!) where the formula is input, the formula looks above in its column to see the numerical value associated with the month (cell T13 in the formula below). For instance, if we are in the 2nd year under the April column, the numerical value is 16 (12 months for the 1st year plus 4 (April) for the 2nd year. Once I have this numerical value (16) I add it to another variable that uses a VLOOKUP function to determine if I need to add 2, 1, 0, -1, -2 months to this value. If the VLOOKUP function returned a -2, then the monthly numerical value is 14 (16 -2 ). I then can use this to look up the data on the other spreadsheet ('S-OEM'!) (that has a similar structure) using INDEX (MATCH) functions returning the column over from the left in which the data resides. This gets around the issue with the "totals" column since, if I'm in a cell next to or within 2 columns of the totals column, my calculation is impacted if I were to just OFFSET by the VLOOKUP value. By using INDEX (MATCH) it finds the correct column every time. But of course there has always got to be a wrench thrown in to make things more difficult...
Once I get to the correct column on the spreadsheet with the data, I'm using the SUMPRODUCT function to compare 4 columns of the same row (all the way down the spreadsheet) and if all 4 conditions are met (which only one row per column meets all 4 criteria) the formula gives me a value 57 rows below the trigger row (where all 4 criteria are met) and in the same column. I then use this SUMPRODUCT value to do other calculations back on the spreadsheet with the formula ('Bus Dev'!). Here is the formula that I'm using that finds the correct value on the data sheet (and it works):
=INDEX('S-OEM'!$H$17:'S-OEM'!$AY$1519,SUMPRODUCT(--('S-OEM'!$G$17:$G$1519="Headcount:")*--(OFFSET('S-OEM'!P$17:P$1519,0,(VLOOKUP('Bus Dev'!$C20,'Bus Dev'!$J$7:$N$10,5)-2))="")*--(OFFSET('S-OEM'!P$17:P$1519,0,(VLOOKUP('Bus Dev'!$C20,'Bus Dev'!$J$7:$N$10,5)-1))="")*--(OFFSET('S-OEM'!P$17:P$1519,0,(VLOOKUP('Bus Dev'!$C20,'Bus Dev'!$J$7:$N$10,5)))>1)*'S-OEM'!$A$17:$A$1519)+57,MATCH('Bus Dev'!T13+VLOOKUP('Bus Dev'!$C20,'Bus Dev'!$J$7:$N$10,5),'S-OEM'!$H$17:$AY$17,0))
The OFFSETs are checking to make sure there is no headcount 2 months prior and 1 month prior to headcount being > 1 in the column where my answer is found. So the problem comes in when I'm within 2 columns of the "totals" column (i.e. months Nov & Dec, and Jan & Feb), because when the OFFSET shifts over 2 columns there is no monthly numerical value under the "Total" column heading.
Below is the part of the formula that does the look up and finds the correct column on the data spreadsheet ('S-OEM'!):
MATCH('Bus Dev'!T13+VLOOKUP('Bus Dev'!C20,'Bus Dev'!$J$7:$N$10,5),'S-OEM'!$H$17:$AY$17,0)
Is there a way, to embed the MATCH function above into the three SUMPRODUCT arguments/arrays?
(I don't necessarily need to embed it in the "Headcount" argument since it is static and will be used in every formula as I copy it across).
Here is the first of the three arguments:
--(OFFSET('S-OEM'!P$17:P$1519,0,(VLOOKUP('Bus Dev'!$C20,'Bus Dev'!$J$7:$N$10,5)-2))="")
If I try replacing the: 'S-OEM'!P$17:P$1519 portion of formula with:
MATCH('Bus Dev'!T13+VLOOKUP('Bus Dev'!C20,'Bus Dev'!$J$7:$N$10,5),'S-OEM'!$H$17:$AY$17,0) I get an error message.
Any suggestions? Thank you very much!!
