Offset and Look value upside from ever changing last corresponding row

**analystbank** · 08-01-2018, 09:38 AM

Sample Workbook with data in version 97-2003 is attached for ready reference.

I have worksheet where I keep data from Row 30 onwards and it keeps expanding.

In order to see what is the last uniques record from Column B having DATE (dd-mmm-yy), I use following formula in Cell C10 to get the last date:

Please Login or Register  to view this content.

In Cell D10, I use following formula to get corresponding Row number to find what that last cell is:

Please Login or Register  to view this content.

Now, I want to retrieve certain values from Column L (data begins from row 30 onward, and will keep expanding along with new dates getting added in Column B).

Since last date / transaction would appear at bottom, I want to scroll up, say 50 (control number, I have stores, to keep flexibility) in Cell F9, and G9.

Now, I am confused, how to use OFFSET function (Or other combination, such that Cell F10 should show Max value, and G10 Min Value from Column L, with following rational.

From Last date appearing in Cell B258 , formula should look through Column L UPWARD side to locate, Max / Min value from Control figure given in Cell F9 & G9.

Currently, I have shown manually in Cell M257, N257 , Max and Min value starting from Row 258 and ignoring that cell, moving range up -50 row upside, Max and Min value.

Last date / data / row will keep increasing day by day, and Control figure (currently 50) may also change to see the other relevant similar details.

Thanks

**FDibbins** · 08-01-2018, 04:17 PM

Your dates show no duplicates, is that co-incidence?

if not, would you be open to using a helper column to give a unique value to each date? Something like =B31+COUNTIF($B$31:B31,B31)/100
Then you could base your search on that

**analystbank** · 08-02-2018, 01:40 AM

There wont be any duplicates, All date will be unique in Column B.

If nothing works, then, Helper column may be thought of.

Issue in given circumstance is for OFFSET function- 'Reference' argument the cell that we refer is not static and will keep moving down which would be the last value in Column B and from there, upside nth value has to be retrieved, making use of Max/Min function.

Thanks

**FDibbins** · 08-02-2018, 02:11 AM

This is what made me comment on that...

In order to see what is the last uniques record from Column B having DATE (dd-mmm-yy),

**FDibbins** · 08-02-2018, 02:30 AM

Try this, I think it does what you want. Although, from the looks of the highlighting in L, you are skipping the very last entry?
=SUM(OFFSET($B$31,COUNTA(B:B)-2,10,-F9,1))
=2018

if you want to exclude the very last entry...
=SUM(OFFSET($B$31,COUNTA(B:B)-3,10,-F9,1))
=2006

**analystbank** · 08-02-2018, 03:37 AM

Thanks Sir. Seems this works.

You are right, I want to exclude very last entry, and for that reason making use of your suggestion as

Please Login or Register  to view this content.

What i observe, is that you are referring the Offset (reference) from Cell B1, which in my work model will be static and should not be a problem, but what

Please Login or Register  to view this content.

, as ROW reference does inside OFFSET formula? can you please briefly narrate, to enlighten me.

Thanks again.

**FDibbins** · 08-02-2018, 03:07 PM

Sure. It was initially -2, 1 for the heading (countA counts everything), and then another 1 because I am actually starting 1 cell below the heading. Maybe this would have been easier to understand...
=SUM(OFFSET($B$30,COUNTA(B:B)-1,10,-F9,1))

Then to skip the last cell in the range...
=SUM(OFFSET($B$30,COUNTA(B:B)-2,10,-F9,1))

**FDibbins** · 08-06-2018, 03:52 PM

Thanks for the feedback

Offset and Look value upside from ever changing last corresponding row

LinkBack

Thread Tools

Rate This Thread

Display

Offset and Look value upside from ever changing last corresponding row

Re: Offset and Look value upside from ever changing last corresponding row

Re: Offset and Look value upside from ever changing last corresponding row

Re: Offset and Look value upside from ever changing last corresponding row

Re: Offset and Look value upside from ever changing last corresponding row

Re: Offset and Look value upside from ever changing last corresponding row

Re: Offset and Look value upside from ever changing last corresponding row

Re: Offset and Look value upside from ever changing last corresponding row

Thread Information

Users Browsing this Thread

Similar Threads

Need help on using the offset function for monthly data changing yearly

Changing time offset in Excel column

drag offset formula with out changing values

upside down

OFFSET problem: Changing dynamic chart

Upside down text?

[SOLVED] Use of offset and match functions with changing arrays, I think???

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions