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

#### SirGruffles

##### New Member
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.

#### Attachments

• 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
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
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
Can you give us the whole formulas that are in F6 & F7?

What version of Excel are you using?

#### SirGruffles

##### New Member

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

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
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

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

#### Peter_SSs

##### MrExcel MVP, Moderator
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
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"?

Replies
3
Views
43
Replies
1
Views
35
Replies
7
Views
446
Replies
7
Views
102
Replies
3
Views
113

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.

### Which adblocker are you using?

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

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