Date range to return certain rows from data stored on separate sheet of workbook

I am SO bad at wording these.

My example will probably show it best.

Basically I want to enter a date in G1 and a date in H1 and have any data from a second sheet, in column A that falls within that range to return that whole row.

Does that even make sense?

Sorry.

I think you have date range wrong? I believe you meant 1/1/2017 - 3/31/2017 from your sample data.

Formula method:
In F8:
=INDEX(Data!$A$5:$A$38,SMALL(IF((Data!$A$5:$A$38>=$G$1)*(Data!$A$5:$A$38<=$H$1),ROW($A$5:$A$38)-4),ROW(A1)))
Confirmed as array (CTRL + SHIFT + ENTER). Then copied down.

In G8:
=INDEX(Data!B$5:B$38,MATCH($F8,Data!$A$5:$A$38,0))

Copy across and down.

But, this sort of operation is best done via Advanced Filter with small bit of VBA.

Please Login or Register  to view this content.

Where A2 contains start date & B2 contains end date. But criteria range is R1:S2.

See attached (AdvFilter sheet) and link below for more on advanced filter.
http://www.contextures.com/xladvfilter01.html

Try this

In I1 and J1 (main sheet) use the following auxiliar formulas

Formula:

Please Login or Register  to view this content.

Formula:

Please Login or Register  to view this content.

In F8 use the following formula and copy down and right (to keep formats use paste special formulas)

Formula:

Please Login or Register  to view this content.

See the file

I do have the date wrong. I meant EXACTLY what you said.

Lemme try to play with it. Let me take the company identifying info (they would skin me if they saw their logo on a forum) and use the actual sheet I will be working with and use the formulas and paste what I come up with. GOSH you guys are BRILLIANT!

OMG I have so much data I started getting lost.

This is the actual sheet. The blank sheet on the first page that needs to have the stuff put into it.

This probably will make it a lot easier. LOL

Hi
Formulas are the same, adjust only the ranges

See the file

Forum expert under your name would be correct. Thank you so much for this!!!


What would I research to see how you came up with this so I can one day (maybe) learn how to do this on my own?

Here I am again. This is an old chart you guys helped me with. I am having trouble converting it to 2018. When I plug in fake data for 2018 on the data tab and type the dates in the upper right corner of the FULL YEAR CALENDAR tab, it does not pull the correct dates like it is supposed to. Can anyone help with this? If you put in the upper right 3/1/2018 to 5/15/2018 you can see what I mean.

My question:

HOW DO YOU KNOW HOW TO DO THIS????? YES I AM SCREAMING!!!! I am an EXTREMELY intelligent person. (If I say so myself)

I have NO idea how to follow that formula or how you even knew where to put anything or what to put. Where would I start in learning this?

You guys are SO brilliant!!!!!

I can't figure out what column AK is doing?

What Jett advised to do worked perfectly.
I started the formula with a =IF('ScheduleData'A$2="","",then inserted his formula. Then I drug it across and down. Perfecto.

I just wish I knew how to do any of this. I get excited if I get a VLookup formula to work on the first try.