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

zookeepertx

Well-known Member
Joined
May 27, 2011
Messages
507
Office Version
  1. 365
Platform
  1. 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: 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:
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))}
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
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,644
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Dec 30, 2008
Messages
12,644
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
May 27, 2011
Messages
507
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
May 27, 2011
Messages
507
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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. :cry:

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

Well-known Member
Joined
Dec 30, 2008
Messages
12,644
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Dec 30, 2008
Messages
12,644
Office Version
  1. 365
Platform
  1. Windows
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.
 
Solution

zookeepertx

Well-known Member
Joined
May 27, 2011
Messages
507
Office Version
  1. 365
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,129,943
Messages
5,639,116
Members
417,073
Latest member
CBG_Christian

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top