Pulling certain data from one sheet to another

Question

Hi Folks,

I have a sheet "Tracker Sheet" that has data in columns A to AY.

I have a second sheet "Weekly Report" that I want to display some data from the first sheet in. In this sheet I have a listbox with week numbers.

• I want to be able to select a week number, and the data be pulled from the first sheet automatically for that week
• As updates are made in the first sheet, I want those updates to show in the second sheet

I was looking at doing this through a macro without using the week numbers listbox, however problems I found were

• I could find all the data I needed on a Monday, but as updates were made during the week turning cells from Blank to non-blank the macro would leave out these original pieces of data and only show the pieces still waiting for input
• I couldnt get the data specific to week number

The way I want this to work is as follows

• Select week number in Cell L17 and year number in Cell N17 on "Weekly Report" sheet
• Data is pulled from the columns A, B, O, Q, AA, AF, AM, AU, AX, in "Tracker Sheet" where values in column X (week numbers) & Y (year) match the values in L17 & N17 on "Weekly Report" sheet

I would appreciate some help on this as I am having real trouble knowing where to start with this

Answer 1

This simple formulas play would get it going for you

Assume data in Tracker Sheet starts in row2 down

In a new sheet,

Criteria Col

Put in A2:

=IF(COUNTA('Tracker Sheet'!X2,'Tracker Sheet'!Y2)<2,"",IF(AND('Weekly Report'!$L$17='Tracker Sheet'!X2,'Weekly Report'!$N$17='Tracker Sheet'!Y2),ROW(),""))

Copy A2 down to cover the max expected extent of data in Tracker Sheet, say down to row 500? 

Extraction -- only the lines satisfying the criteria will be extracted, all neatly packed at the top

Place this in B2:

=IF(ROWS($1:1)>COUNT($A:$A),"",INDEX('Tracker Sheet'!A:A,SMALL($A:$A,ROWS($1:1))))

Copy B2 across as far as required (across to AY2, going by your specs), fill down by the smallest range required to cover the max expected # of line returns for any criteria set, eg down to row 50 will do? Dress it up by deleting away all the intervening cols that you don't need extracted. In your deletion, just refer to the "INDEX('Tracker Sheet'!A:A, .. " part of expression in the cell, that's the source col extracted

Answer 2

It didnt work at first until I did a bit of playing around with it (mainly due to issues with my data) but got it in the end.

Thanks very much for your time and assistance

Answer 3

Hey Folks,

Apologies about the late response, been out sick.

Tom-S, you array works great to pull data from one sheet to another based on the details put into L17 & N17 however it doesnt do everything I need and its my own fault in that I didnt explain myself better.

See update below in bold

The way I want this to work is as follows

- Select week number in Cell L17 and year number in Cell N17 on "Weekly Report" sheet

- Data is pulled from the columns A, B, O, Q, AA, AF, AM, AU, AX, in "Tracker Sheet" where values in column X (week numbers) & Y (year) match the values in L17 & N17 on "Weekly Report" sheeton each row

The array formula you gave works perfect for pulling the first instance of a match, but will not pull data for additional matches

I would really appreciate some help with this as this is beyond me

Answer 4

Hi,

It sounds to me like you could use pivot tables and slicers to achieve this (assuming you are using Excel 2010)

Simply create a pivot table on your "Weekly Report" sheet with all of the columns on "Tracker Sheet" as the data range. You could then use slicers for week number and year number to display the specific data. You may have to play with the pivot table options to encourage it to display the data the way you want it!

Then the only thing you would have to do is hit "refresh all" in your pivot table workbook whenever you update the data sheet. You would need both workbooks open at once to do this.

Does this help?

Graham

Answer 5

DaveCorley,

To pull the data from column A of Tracker Sheet you could try this array formula (has to be confirmed with CTRL+SHIFT+ENTER, not just ENTER alone):

=INDEX('Tracker Sheet'!A1:A1000,MATCH(L17&N17,'Tracker Sheet'!X1:X1000&'Tracker Sheet'!Y1:Y1000,0))

You need to adjust the ranges to suit exactly where your data is on Tracker Sheet.

To pull data from column B just change the A's to B's; to pull data from column O just change the A's to O's etc.

You may want to wrap an IF function around it to deal with blank cells on the Tracker Sheet, along the lines:

IF(INDEX(....)="","",INDEX(....))

___________

Regards, Tom