Selecting Data over a Date Range

**Supdem** · 09-12-2009, 11:25 PM

I have 2 worksheets where I need to transfer a certain amount of data from one to another depending on dates.
Sheet ‘A’, is the main data source. Col A holds all 365 dates from 1 Jan to 31 Dec(starting at A2), and the subsequent data for each day is held in cols B to P.
Sheet ‘B’ is the destination where the user will specify a key date in cell A3, then in A4 to A32, formulae populate the next 29 dates (ie so there is a continuous run of 30 days).
What I need to do, is to identify the same 30 day range from sheet B on sheet A, then to copy the data in Cols B to P for that range back onto sheet B (pasting in at D3)
Hope this is clear.
Many Thanks.

**JBeaucaire** · 09-12-2009, 11:30 PM

Put this formula on Sheet2 cell B3 and copy it down 30 rows and across to column P.

=INDEX(Sheet1!B:B, MATCH($A3, Sheet1!$A:$A, 0))

In A4 and copied 28 rows down is the formula:
=A3+1

Now any changes to A3 will reformat the displayed data instantly.

EDIT: sorry, put that first formula in D3 instead of B3 if that's where you want the first set of data to appear, then copy down and across.

**Supdem** · 09-13-2009, 12:01 AM

BRILLIANT !!

Thanks a million mate, thats exactly waht I was after - really do appreaciate the very fast response

**Supdem** · 09-14-2009, 07:05 PM

Hi JB
Since I thanked you for the solution you supplied, I now have to ask for a further spot of help! When I posted my original query, for the sake of clarity, I simplified the worksheets, data location etc, and I was indeed able to execute the solution you supplied and it did just what I was after. However…..
My plan was then to implement your solution to my working spreadsheet, but my attempts keep failing. Your solution cited A:A for the Index array. I changed this to D:D to match my working spreadsheet, but Excel then brought up an ‘Update Values’ window and when I cancel that, it turns the D:D to D$1:D$65536 . Im obviously doing something wrong but its not apparent to me!

I’ve attached my working spreadsheet with the source data in ‘2009’ and in the destination sheet of ‘Claim’ you will see a single row of my attempt at implementing your solution. Sheets 1 and 2 are populated with a working example of your solution so theres proof it works. Could you tell me where Im going worng?

Many Thanks – again!

**JBeaucaire** · 09-14-2009, 07:39 PM

Your profile shows Excel 2003, but you uploaded a 2007 file. Interesting.

Well, the first thing that jumps out is that your sheet is called "2009" but in the formula you tried to use "Sheet2009". That's not the same.

**Supdem** · 09-14-2009, 07:48 PM

Ooops yes, sorry, at work they've got 2007 and Ive had to upgrade to follow. I'll amend that.

With regard to the 'sheet's, C8 has Sheet2009 and D8 etc have '2009' I was just trying out anything to coax it into life.

Are you getting the same Update values window as I was?

**JBeaucaire** · 09-14-2009, 08:32 PM

Yes, it is caused by Excel desparately searching for this Sheet2009. Fix all of those, save your sheet, reopen, it should stop.

**Supdem** · 09-15-2009, 04:46 AM

GOT IT
The problem was with the Lookup array at the end of the formula - I had left it as $A:$A instead of $D:$D Duh!

Does A:A or $A:$A mean anything in col A?

Many Thanks Jerry

**JBeaucaire** · 09-15-2009, 11:30 AM

Originally Posted by Supdem

Does A:A or $A:$A mean anything in col A?

Yes, it means evaluate the whole column (if necessary).

A:A will change to B:B and C:C as the formula is copied across columns.
$A:$A will not change as the formula is copied across columns.

===========
If that takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].

(Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated)