Formula that pulls from a specific row for each month & "locks" into that row for the month?

SirGruffles

New Member
Joined
Jul 23, 2018
Messages
16
Apologies if this looks/sounds messy...

In the middle of my (probably unnecessarily complex/large) formula, I have this function: (OFFSET(INDIRECT(ADDRESS(ROW(), COLUMN())),-ROW()+2,0))
As it stands, this will pull the value of the cell 2 rows from the top, in the same column as the current cell.
While this works great for the first "area" of data (January 2020 in this instance), if I copy it down into the sections for other months, it will continue to pull January data only.

I attached a fragment of the worksheet to this post for some visual assistance.

The type of formula I'm looking for, in words I can only hope I'm using correctly:
1) Would function similarly to what's currently there, but
2) Would allow for pasting down into other months
a) In the picture, the 39 & 134 are correct in the January area, but are incorrect for the February area, as February has not occurred yet. It is still pulling that 1/4/2020 date value from the 2nd row.
b) Using a different variation of OFFSET... allowed me to easily code the "future" months, but gave me issues when pasting down the line of the current month. As going from Row 6 to Row 7 would take the OFFSET cell's value a cell down.

Is there a different way to use OFFSET and ADDRESS that would allow me to "lock" that specific month row (rows 2 & 69 in the picture) in their respective months? (Instead of my -ROW()+2 formula).

If more of the formula is needed for clarity, let me know and I can post more.

The goal here is to create a formula I can use in each month's sections, and not having to create a different one for each month OR for each line (A,B,etc).

I appreciate any advice, tips, ideas.

Thank you for your time.
 

Attachments

  • Example Dashboard Section.PNG
    Example Dashboard Section.PNG
    16.3 KB · Views: 11

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,474
Office Version
  1. 365
Platform
  1. Windows
Do I need to do anything to mark as "solved"?
No, what you said earlier in your post is plenty. We don't mark threads as solved in this forum as it sometimes happens that an even better solution comes along later and we don't want to discourage that by marking a 'solved'.

BTW, you're welcome & thanks for the enthusiastic response. :)

Edit: Also, if you were able to replace all those OFFSET and INDIRECT functions, your worksheet should be more responsive speed-wise. (y)
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

SirGruffles

New Member
Joined
Jul 23, 2018
Messages
16
That's my next goal with this formula: the replacement of the Offset/Indirects.

The first goal was to get something functional for my Operations team.
Up next is making it efficient.
 

SirGruffles

New Member
Joined
Jul 23, 2018
Messages
16
Welp, that turned out to be a quicker fix than I anticipated...

Thanks to your help, this:
=IFERROR(IF(MONTH((OFFSET(INDIRECT(ADDRESS(ROW(), COLUMN())),-ROW()+2,0))-7)=MONTH((OFFSET(INDIRECT(ADDRESS(ROW(), COLUMN())),-ROW()+2,-22))),SUMIFS(INDEX('Daily Dashboard Datalink V2'!$E:$KZ,MATCH($B6&P$5,'Daily Dashboard Datalink V2'!$A:$A,0),0),'Daily Dashboard Datalink V2'!$E$2:$KZ$2,"<="&(OFFSET(INDIRECT(ADDRESS(ROW(), COLUMN())),-ROW()+2,0)),'Daily Dashboard Datalink V2'!$E$2:$KZ$2,">"&(OFFSET(INDIRECT(ADDRESS(ROW(), COLUMN())),-ROW()+2,0))-6)),SUMIFS(INDEX('Daily Dashboard Datalink V2'!$E:$KZ,MATCH($B6&P$5,'Daily Dashboard Datalink V2'!$A:$A,0),0),'Daily Dashboard Datalink V2'!$E$2:$KZ$2,"<="&((OFFSET(INDIRECT(ADDRESS(ROW(), COLUMN())),-ROW()+2,0))),'Daily Dashboard Datalink V2'!$E$2:$KZ$2,">"&((OFFSET(INDIRECT(ADDRESS(ROW(), COLUMN())),-ROW()+2,0))-DAY(OFFSET(INDIRECT(ADDRESS(ROW(), COLUMN())),-ROW()+2,0))+1)))

is now this:
=IF(MONTH(INDEX(P$1:P6,AGGREGATE(14,6,(ROW(P$1:P6)-ROW(P$1)+1)/(P$1:P6="Actual"),1)-2)-7)=MONTH(INDEX(P$1:P6,AGGREGATE(14,6,(ROW(P$1:P6)-ROW(P$1)+1)/(P$1:P6="Actual"),1)-2)),SUMIFS(INDEX('Daily Dashboard Datalink V2'!$E:$KZ,MATCH($B7&P$6,'Daily Dashboard Datalink V2'!$A:$A,0),0),'Daily Dashboard Datalink V2'!$E$2:$KZ$2,"<="&(INDEX(P$1:P6,AGGREGATE(14,6,(ROW(P$1:P6)-ROW(P$1)+1)/(P$1:P6="Actual"),1)-2)),'Daily Dashboard Datalink V2'!$E$2:$KZ$2,">"&(INDEX(P$1:P6,AGGREGATE(14,6,(ROW(P$1:P6)-ROW(P$1)+1)/(P$1:P6="Actual"),1)-2))-6),SUMIFS(INDEX('Daily Dashboard Datalink V2'!$E:$KZ,MATCH($B7&P$6,'Daily Dashboard Datalink V2'!$A:$A,0),0),'Daily Dashboard Datalink V2'!$E$2:$KZ$2,"<="&((INDEX(P$1:P6,AGGREGATE(14,6,(ROW(P$1:P6)-ROW(P$1)+1)/(P$1:P6="Actual"),1)-2))),'Daily Dashboard Datalink V2'!$E$2:$KZ$2,">"&((INDEX(P$1:P6,AGGREGATE(14,6,(ROW(P$1:P6)-ROW(P$1)+1)/(P$1:P6="Actual"),1)-2))-DAY(INDEX(P$1:P6,AGGREGATE(14,6,(ROW(P$1:P6)-ROW(P$1)+1)/(P$1:P6="Actual"),1)-2))+1)))

Hm, thought it would look shorter than that...
But regardless, all of the OFFSET & INDIRECT's have been replaced using your suggested INDEX & AGGREGATE formula.

Thank you again for this!
😄
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,474
Office Version
  1. 365
Platform
  1. Windows
I haven't tried to follow that formula in detail but it certainly looks better without the OFFSETs and INDIRECTs. :)
 

Watch MrExcel Video

Forum statistics

Threads
1,129,387
Messages
5,636,010
Members
416,892
Latest member
Bensch

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