Use cell location of SMALL array formula as OFFSET reference?

[mattmars]

Hello there!

I've searched everywhere for a solution, hoping you all can lend a hand. I have a long list of dates and associated information that cannot be sorted or filtered, but that I need to manipulate nonetheless. To get around this, I have a formula that will create a new list of dates from oldest to newest that fall between a user-specified specified range (CHART!F8 and CHART!F7 are the end and start dates):

{=iferror(((SMALL(IF(($H:$H<=CHART!$F$8)*($H:$H>=CHART!$F$7),$H:$H,"H"),ROW(H1)))),"")}

I copy this however many times down a column and it appears to work fine on its own to grab the appropriate dates from H, but I can't figure out how to get the associated information from the original table that corresponds to each date, to build basically a filtered clone of the original table. The only things I can think to do are either somehow nestle the above formula in OFFSET and capture the info I need in the 'width' section, or use ADDRESS or CELL or something to get the cell location of the formula result and use that in an OFFSET formula. The problem is I can't get either to even come close to working.

Hopefully that made sense, thanks again for any assistance.

[oeldere]

is a pivot table acceptable.

if so, please add a samlle excel file, without confidential information.

please also add the expected result in the file (manualy)

[mattmars]

Thanks for the reply.

Attached is the (admittedly inefficient and bloated) file I'm currently working with. Basically the end result is an Excel 'database' (I don't have access to change or make new queries to the actual database that produces the raw data, so I had to improvise my own) that the user will 'query' to check productivity over a specified time period ('CHART' worksheet).

In the 'ARCHIVE' sheet, a macro pastes in new data copied from an Access query along the left-hand side (the blue columns), then a variety of formulas (columns S:AE) deconstruct and rebuild the info into a usable form. The problem I had was that originally the formulas in S:AE were to be copy/pasted down based on the data in the blue columns and cleared out (all by macro) once the data was reconfigured, but even that was too memory-intensive.

So I came up with my current plan to simply pull the needed data into a new table, and apply the formulas to that smaller table (the red columns). In the red columns I've plugged in fake data to show what I want to happen. The user has designated a time range between 9 June and 16 June 2016, so that should be the only data in the red columns, sorted oldest to newest.
Once the red columns are working correctly, the formulas in S:AE will be altered to refer to those cells instead of what they currently reference.

Anyway, I'm not sure if a pivot table is an option, I've had only limited exposure to the concept. I'm willing to do anything that works at this point, but even if the solution is different than I had imagined I'm still curious about the idea of getting the cell address out of array formulas. I guess in a way I'm in more of a 'can I?' than 'should I?' mindset.

Again, hopefully that makes sense, and thanks!

[JeteMc]

Try this array entered formula* in AH2:

Please Login or Register  to view this content.

*After pasting the formula press the F2 key, then press Ctrl, Shift and Enter simultaneously. You can then double click to have the formula copied down to AH87 and, while AH2:AH87 are still selected pull across to AQ87.
Let me know if you have any questions.

[mattmars]

I'm not sure what's more impressive, that you solved the problem, or understood that mess of a workbook in the first place. Thank you so much!

[JeteMc]

You're Welcome and thank you for the feedback. Please take a moment to select Thread Tools from the menu link above and mark this thread as SOLVED. I hope that you have a blessed day.