zookeepertx
Wellknown Member
 Joined
 May 27, 2011
 Messages
 507
 Office Version

 365
 Platform

 Windows
I've been trying to figure this out for almost 2 days now and I surrender!!
I have a workbook that has 5 sheets in it, however, I'm only concerned with 2 of them: Sheet1, which is named "MONTH END TOTALS" and sheet3, which is named "COMPLETE". As you might guess, "MONTH END TOTALS" summarizes the data from "COMPLETE". "COMPLETE" is constantly having data added on to the end of it, so "MONTH END TOTALS" has a number of tables on it, summarizing data for a given year. (Each table ends up with data for the fiscal year: AugJul). The table I'm looking at is for the current FY  2020.
On "MONTH END TOTALS" C54  N54 are filled with the months Aug = Jul. The FY is located in C53. There's a formula in O53 that shows what the last row of data on "COMPLETE" is. The table itself goes from C55 to N63.
On "COMPLETE" the data in column A is all just dates, covering a number of years.
The first thing I need to do is to have C55 return how many rows on "COMPLETE" are in the month from C54 (Aug) & year from C53 (FY2020). So, the range to look at on "COMPLETE" is A2A(last row of data, found in O53 on "MONTH END TOTALS").
Here is the formula I have in C55, which actually does what I need:
That code carries on clear to N55, adjusted for the correct month in the given column. (There are other formulas down through the rest of the rows in the table, but they're based on this formula, so, if I can get this to work, I can adjust those.)
NOW  on to my problem...
Since each fiscal year will be represented by copy/pasting the table below and then again the next year and so on, I'd end up having to replace C54 with C67 and C55 with C68 and then continue that on each subsequent year. Each year has 60 cells that these replacements would require! VERY tedious! Plus, all the cells with formulas are locked so the users can't screw the thing up, LOL!
Is it possible to have the formula in  for example  C55 use its own address as the reference in the OFFSET part of the formula? In other words, the bit of the formula that now says OFFSET(C55,2,12) instead say something like OFFSET([the cell the formula's in], 2, 12). This would save from having to adjust all those formulas each time the table is pasted below this one.
I hope I've explained this somewhat clearly enough. Truthfully, the whole workbook is VERY slow, I assume because of all the INDIRECT and OFFSET formulas, but it's the only way I could find to get the answers I needed.
Thank you for anybody that can help me!
Jenny
I have a workbook that has 5 sheets in it, however, I'm only concerned with 2 of them: Sheet1, which is named "MONTH END TOTALS" and sheet3, which is named "COMPLETE". As you might guess, "MONTH END TOTALS" summarizes the data from "COMPLETE". "COMPLETE" is constantly having data added on to the end of it, so "MONTH END TOTALS" has a number of tables on it, summarizing data for a given year. (Each table ends up with data for the fiscal year: AugJul). The table I'm looking at is for the current FY  2020.
On "MONTH END TOTALS" C54  N54 are filled with the months Aug = Jul. The FY is located in C53. There's a formula in O53 that shows what the last row of data on "COMPLETE" is. The table itself goes from C55 to N63.
On "COMPLETE" the data in column A is all just dates, covering a number of years.
The first thing I need to do is to have C55 return how many rows on "COMPLETE" are in the month from C54 (Aug) & year from C53 (FY2020). So, the range to look at on "COMPLETE" is A2A(last row of data, found in O53 on "MONTH END TOTALS").
Here is the formula I have in C55, which actually does what I need:
VBA Code:
{=SUM(IF(TEXT(INDIRECT("COMPLETE!$A$2:$A$"&(OFFSET(C55,2,12))),"*MM/YYYY")=((MONTH(C$54&1))&"/"&(OFFSET(C55,2,0))1),1,0))}
NOW  on to my problem...
Since each fiscal year will be represented by copy/pasting the table below and then again the next year and so on, I'd end up having to replace C54 with C67 and C55 with C68 and then continue that on each subsequent year. Each year has 60 cells that these replacements would require! VERY tedious! Plus, all the cells with formulas are locked so the users can't screw the thing up, LOL!
Is it possible to have the formula in  for example  C55 use its own address as the reference in the OFFSET part of the formula? In other words, the bit of the formula that now says OFFSET(C55,2,12) instead say something like OFFSET([the cell the formula's in], 2, 12). This would save from having to adjust all those formulas each time the table is pasted below this one.
I hope I've explained this somewhat clearly enough. Truthfully, the whole workbook is VERY slow, I assume because of all the INDIRECT and OFFSET formulas, but it's the only way I could find to get the answers I needed.
Thank you for anybody that can help me!
Jenny