Index, Small, Row formula, need to add reference to date period

Hi Gary

Why not upload a copy of your sample data with a full description of what you want.
There may be other / easier ways of achieving your goal.

As you are using XL 2010, you can simpliufy all of your formulae anyway, by using the IFERROR function rather than If ISERROR

IFERROR(INDEX(Datasheet!$A$1:$Z$10000,SMALL(IF(Datasheet!$D$1:$D$10000=$C$39,ROW(Datasheet!$D$1:$ D$10000)),ROW(Datasheet!1:1)),"")

Hi Roger

Thank you for getting back to me so quickly, I am new to Forums so am only just getting to grips with how these work.

I have set up an example workbook in excel, I just hope I have attached it correctly!

Many thanks

Hi Gary

The attached workbook is a .xlsm file as I have included 2 small macros. I hope that you are allowed to use a VBA solution

Basically, you can achieve what you want using Advanced Filter, rather than having those horrible array formulae, which are very slow.

Firstly, I created a table of your data by placing my cursor inside your data on Sheet Datasheet then Insert tab>Table>my Table has headers and then I changed the default name given by Excel to myTable.
The advantage of a table is that it will constantly resize as you add more and more data.

On your report sheet, I inserted a few rows at the top, and I then use B1:D2 to hold the criteria for the advanced filter.
I B1 I typed Ledger, and in C1 and D1 I typed Date.
In B2 I entered to formula =C8 to pick up the selection you make via your dropdown
In C2 I entered the formula =">="&F7 which then turns into >= whatever start date you choose in cell F7, and similarly for C2 it is ="<="&F8 for the end date. The dates show up as serial numbers rather than being formatted as dates.

I then created a defined range called myCrit, which refers to range B1:D2
I defined a range called myDest which refers to your column headings in E13:H13

I then created a small macro to run the Advanced Filter, as opposed to manually running it each time

Please Login or Register  to view this content.

So this telling advanced filter to use all of the data in the table mydata, filter it according to the criteria set up in myCrit, and copy the result to the range defined by myDest

The final stage is to automate things, so that as soon as you change the selection in C7, or F7 or F8, then run the macro. (you could just run the macro each time manually, but this just automates it).

Please Login or Register  to view this content.

This second code sits as event code on the sheet Report.

You can hide the first 5 rows on Report Sheet if you wish.

Roger

I can't thank you enough for your help.

It now works just as I needed it to

Many thanks

Sorry to come back to this thread but having tested the table in a live workbook I have come across a problem which I can't seem to get around.

The live datasheet needs to be locked to prevent users overtyping formulas and conditional formatting doesn't seem to copy down to the next row in the table.
We already have a vba routine that adds new rows when needed and have set up a dynamic name range using the offset formula.

Is there another way of producing the results I need without resorting to a table?

Bump no response

I don't know whether to "Bump no response" or to give more information, so I have done both!

My original request was to be able to extract rows of data from a datasheet into a report sheet within a date range, due to the kindness of a contributor I have managed to reduce the array formula to:

Please Login or Register  to view this content.

This formula works to extract all instances of whatever is determined by C1 on the report sheet, i.e. from all the rows in the datasheet - see previous attachment.

What I need to do is extract the data as above but within a specific date range determined on the report sheet - Column A on the datasheet is the date column, I cannot extract the report through a table due do a table's limitations (not being able to protect it was the biggest drawback), is it possible to edit/add to the above array formula to include these two extra conditions?

Or do i need to go down the VBA route?

Any help would be greatly appreciated.