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

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

CSmith

Well-known Member
Joined
Jan 13, 2020
Messages
686
Office Version
  1. 365
  2. 2010
  3. 2007
Platform
  1. Windows
  2. Mobile
  3. Web
An EXACT number offset sounds like what you want but without more data and better explanation not much more I can offer :)
 

SirGruffles

New Member
Joined
Jul 23, 2018
Messages
16
What other data would you like to see?

If by "exact" number offset, you mean something to the effect of: OFFSET(cell,-4,0)?

In the attached image from the post, the same formula are in the yellow cells.
But because of the (OFFSET(INDIRECT(ADDRESS(ROW(), COLUMN())),-ROW()+2,0)), it's always hitting that "1/4/2020" in cell F2.
Even when I paste the formula into the February section, it will still pull the value of cell F2.

Is there a way to get this offset formula (or a variation of it) that would allow me to paste into the cell in F73, so that it would pull the data from F69?
But going past that, so that when I drag it down to F74, it will still pull the data from F69?
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,500
Office Version
  1. 365
Platform
  1. Windows
Can you give us the whole formulas that are in F6 & F7?

What version of Excel are you using?
 

SirGruffles

New Member
Joined
Jul 23, 2018
Messages
16

ADVERTISEMENT

Oh boy...I sure can....please don't judge me too hard:

F6: =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)))

F7: =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($B7&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($B7&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)))

As for version, I'm seeing Excel for Office 365 MSO - 64 bit.
I'm not seeing a year version...my apologies.
 

SirGruffles

New Member
Joined
Jul 23, 2018
Messages
16
In my desire to reply as quick as possible, I neglected to remember that F6/F7 are indeed the same formula.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,500
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

please don't judge me too hard
OK, yes, they are rather cumbersome. :)

I had hoped to offer a whole alternative formula but I am not able to replicate what you have or want as I also don't have the 'Daily Dashboard Datalink V2' sheet & its data.
Never-the-less, see if this is some help. If all your sections have the word "Actual" 2 rows below the row you want to access with your formula (or we could use 'Processed Output' 3 rows below) then you can use a formula like I have shown in F6, drag/copy it to F7 then copy both and paste in F73:F74 and any other sections to pick up that sections date.

Book1
F
1
21/04/2020
32
4Actual
5Processed Output
61/04/2020
71/04/2020
66
67
68
692/08/2020
705
71Actual
72Processed Output
732/08/2020
742/08/2020
75
Sheet1
Cell Formulas
RangeFormula
F6:F7F6=INDEX(F$1:F5,AGGREGATE(14,6,(ROW(F$1:F5)-ROW(F$1)+1)/(F$1:F5="Actual"),1)-2)
 

SirGruffles

New Member
Joined
Jul 23, 2018
Messages
16
Let me give this a try and come back (hopefully) shortly.

I'm afraid of posting the entire file, both due to overall size, but also feel like it's a breach of "security" of my company to divulge so much production info.
I can try to shrink and rename as much as possible if your above solution doesn't pan out.

Thank you for your time.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,500
Office Version
  1. 365
Platform
  1. Windows
Shouldn't need the whole file. If you can't adapt what I have given you, perhaps you could make a very small file with dummy data to replicate what is required. If we get to that, please don't just post images as we cannot copy from those to test. XL2BB (see my signature block below) is one way to post small sample data (that we can copy) directly here in the forum.
 

SirGruffles

New Member
Joined
Jul 23, 2018
Messages
16
Sweet baby Jesus....it works! It's perfect! 😂
This **** spreadsheet is finally as dynamic as I can make it, with the formatting being "locked" by upper management.

Now, I need to go read up on this Aggregate function, as I've never used this one before...

(and noted on the XL2BB for sample data tables. will do so in the future)

Thank you so much for your help on this.
This has been driving me crazy for the past week and a half.

Do I need to do anything to mark as "solved"?
 

Watch MrExcel Video

Forum statistics

Threads
1,129,587
Messages
5,637,234
Members
416,963
Latest member
samfuge

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