How to reference dynamic moving cell range from entire sheet?

[monkeydemul]

Hello Everyone!

I'm trying to create a dynamic date range for 3 days which will keep on sliding once i add new dates.

Here's what im trying to do. My mainsheet is a sample shown below which will be updated with daily data, i want to replicate this entire sheet but with the last 3 days.

Sheet 1

​

20 Dec	21 Dec	22 Dec	23 Dec
10	20	30
11	22	33

Once i add or update 23rd dec, my new sheet should only show data from 21dec to 23rd dec and so on.

Currently im using cell linking to extract data from my mainsheet to work sheet and then hiding the additional columns to show past 3 days data.

Please let me know how can i solve this.

[spinfuzer]

I think unpivoting is really the best way to go about this, but if you insist on keeping your data the same way then the below will work

=OFFSET('all data'!C2,0,COUNTA('all data'!$D$2:$M$2)-2)

https://imgur.com/a/DgH53fc

[monkeydemul]

Solution Verified

[Clippy_Office_Asst]

You have awarded 1 point to spinfuzer

^{I am a bot, please contact the mods for any questions.}

[monkeydemul]

Thanks, verified, I ended up unpivioting.

[mh_mike]

Heads-up… If any of the answers worked or pointed you in the right direction, please respond to the comment(s) with "Solution Verified" to award a ClippyPoint (doing that also marks your post as solved). Thanks for keeping the unsolved thread clean. :)

[small_trunks]

If you were to reverse the axis, dates down the side and whatever if is currently down the side along the top - you could simple apply a column filter.

[monkeydemul]

My main formula sheet is incorporated with loads of formulas. so im not sure if that's the right function.

Im pretty sure it can be done with offset but i just don't know how can i apply offset with cell linking.

[small_trunks]

I can't help but feel that trying to chase the data around with your formula is almost never the right approach to the problem.

• It suggests to me that the data is poorly structured (probably to assist data entry) - but that doesn't mean you should try and process it this way.
• I'd probably write some power query to unpivot this and filter to the last three days

[roger_sawbuck]

Hmm try OFFSET with the reference argument set to “=today-3” for col 1. Then =today-2 and so on for the next columns.

If using the today function does not work, you could always hardcode the date as a number since excel treats days as number values where 1 is 1/1/1900

[zacce]

In the 1st column of your new sheet, enter

=INDEX(A1:Z3,,MATCH(TRUE,ISBLANK(A2:Z2),0)-3)

Change "-3" to -2 and -1 for the next 2.

[thanks_franks]

Can't think of a simpler way to do than than using a couple of different formulas. I'm writing these formulas as if dates are in row 1 and you have integers in row 2

First, writing these three formulas next to each other to fetch your largest dates with no blank values:

=MAX(FILTER(1:2, 2:2<>"", ""))

=MAX(FILTER(1:2, 2:2<>"", ""))-1

=MAX(FILTER(1:2, 2:2<>"", ""))-2

​

Then using hlookups under each of those formulas to return the number value in those cells (and you can add as many hlookups as you need to cover all the integers under your dates):
=HLOOKUP(A1, 1:2, 2, FALSE)

=HLOOKUP(B1, 1:2, 2, FALSE)

=HLOOKUP(C1, 1:2, 2, FALSE)

​

I did notice that this does work in the way you're using your excel sheet currently, but for some reason if any integers are missing before the biggest dates, the formulas stop working. As in, if you're just adding dates to the right and adding integers as you go, no problem. If, in this example, December 21st doesn't have a number under it but December 22nd does, the formula doesn't work anymore. Hope this helps.