Offset formula using the cell the formula is in as a reference point.

[zookeepertx]

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: Aug-Jul). 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 A2-A(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:

{=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))}

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

 

[jasonb75]

You can do what you're asking with OFFSET(INDIRECT("RC",0),-2,12) the part that I've changed, INDIRECT("RC",0) always refers to itself.

If you double check that it does what you need, I'll take another look at the formula and see if there is a way to do what you need without volatile functions.

 

[jasonb75]

Too late to edit my post above. I overlooked something simple. C55 in your original formula is relative, so it should adjust automatically as long as you copy the cell, not the formula.

I think that this is correct as a non-volatile alternative, but without a sample sheet and expected results for testing / comparing, I could be way off

=COUNTIFS(Complete!$A$2:INDEX(Complete!$A:$A,INDEX(O:O,ROW(C55)-1)),">="&DATEVALUE(MONTH(C$54&1)&"/"&INDEX(C:C,ROW(C55)-1)),Complete!$A$2:INDEX(Complete!$A:$A,INDEX(O:O,ROW(C55)-1)),">="&EOMONTH(DATEVALUE(MONTH(C$54&1)&"/"&INDEX(C:C,ROW(C55)-1)),0))

 

[zookeepertx]

Thank you! I'll have to check tomorrow on how it works.

Oh, a while back we changed to Microsoft 365 (which I hate) if that makes a difference.

 

[zookeepertx]

Hello again,

Your first suggestion did seem to work correctly, although I didn't get a chance to try it on numerous cells because I saw your second suggestion and moved on to it. I assume that getting rid of all the INDIRECTs and OFFSETs would hopefully speed things up, right? However, I'm unable to get that to work at all! It keeps returning a zero. I've tried adjusting numerous things in the formula and the spreadsheet but can't make any headway.

One thing: In C-G, the formula needs to refer to the year BEFORE the year in C53. This is because our fiscal year runs from Aug-June, so Aug-Dec (C-G) should use the data from 2019. Then H-N (Jan-Jul) should use the data from 2020, if that makes any sense. That's why I had to have my original formula have the -1 in the part near the end of the formula that said (OFFSET(C55,-2,0))-1) ; to make it look at 2020 and make it 2019 for Aug-Dec.

I see a button to "Upload Image" but I don't find a way to upload a workbook. Am I missing something?

Thanks!

Jenny

 

[jasonb75]

You can't upload a full workbook, but if you click on the XL2BB button there is an add in that allows you to post tables with formulas, formatting, etc to the forum.

The countifs formula that I suggested was the result of trying to analyse your original formula, without data to refer to it was a bit of guesswork in places. Getting a bit late my side of the atlantic, but I'll have another look in the morning to see if I can find where I've gone wrong.

 

[jasonb75]

I found a number of errors in my second formula, not sure if I've got it right this time, but it should be closer than the previous attempt.

=COUNTIFS(Complete!$A$2:INDEX(Complete!$A:$A,INDEX(O:O,ROW(C55)-2)),">="&DATEVALUE(MONTH(C$54&1)&"/"&INDEX(C:C,ROW(C55)-2)),Complete!$A$2:INDEX(Complete!$A:$A,INDEX(O:O,ROW(C55)-2)),"<="&EOMONTH(DATEVALUE(MONTH(C$54&1)&"/"&(INDEX(C:C,ROW(C55)-2)-1)),0))

I can't say for certain if changing to this formula type will speed up the processing time without knowing if the INDIRECT formulas are the root cause or if there are other resource demanding processes in the workbook, but hopefully it will make a difference.

 

[zookeepertx]

I was looking back at some of my previous posts and realized that I never thanked you properly for your help with this! I know it was a very complicated question and was, no doubt, a bear to figure out the answer to, but your solution did work and its been a big help for my coworkers.
So, I'm just posting to say thanks and hope you're having a great day!

Jenny