Need to get a sheet to Lookup values corresponding to names from sheets sorted by date.

[ostego]

Hi,

I Have a spreadsheet separated by days (i.e. one sheet = one day). Each sheet contains a lot of names in column A, a value in column B corresponding to the name, and another value in column C.

I now need to create a single sheet that shows all of these values across 30 days for just 15 of the names, and was thinking there must be a way of using a LOOKUP function : By which I can tie a whole column on my output sheet, to look in the sheet that corresponds to that date, and then it will find the values for that name if it is on that sheet.

I have attached an example of how I'd need the data to look, along with some example sheets to show how the data would be formatted (very basic example of course).


This download should work : Excel example.xls

If not, I uploaded it here : http://speedy.sh/3rH6z/Excel-example.xls


Any help would be extremely appreciated

[etaf]

link is not working

[rcm]

I could not open the attachment... I am planning to use the indirect function to retrieve from each tab the info. which of the 2 values b or c, do you want to summarize?

[ostego]

http://speedy.sh/3rH6z/Excel-example.xls

Does that links work?

I want both data values A and B for each. The main thing I need though is for it to Look up and filter out the info using the names

The data I want to look like this (i have added in the dots so it formats correctly)

..........01/06....02/06.....03/06
Orange....1.........8..........2
..............2.........9..........1

Apple .....2.....................4
.............3.....................5

Pear.......3........9
.............4........8


----------------

The sheets for those days would be like :

Sheet1 : 01/06

Fruit A B
Orange 1 2
Lemon 3 4
Apple 2 3
G.Fruit 5 5
Cherry 3 3
Pear 3 4


Sheet 2: 02/06

Fruit A B
Orange 8 9
Cherry 7 8
Banana 4 8
G.fruit 3 5
Pear 9 8


Sheet 3 : 03/06

Fruit A B
Orange 2 1
S.berry 5 3
Tomato 3 4
R.berry 5 5
Apple 4 5
Kiwi 2 1

[oeldere]

I downloaded the xls file.

Why not add all data on 1 sheet (e.g. with an macro).

After that you can use a filter or pivot table to analyze the data.

[ostego]

I downloaded the xls file.

Why not add all data on 1 sheet (e.g. with an macro).

After that you can use a filter or pivot table to analyze the data.

That is what I was originally thinking. However, the issue I came across was getting a Macro that would recognise the date each time, and if the name wasn't found under that date, as some days there will be no value for the person under a certain date as they will not have been working.

I am pretty bad with macros though to be honest as well as pivot tables, so if someone could help me out there I would be equally overjoyed!

[oeldere]

To get you started:

The macro below will add all data in sheet summary.

After that you can use another macro to get all info in rows instead of columns.

See the attached file.

Please Login or Register  to view this content.

[oeldere]

This code will re-arange the data, so you could easy use a pivot table.

See the attached file.

Please Login or Register  to view this content.

[sktneer]

I didn't download your worksheet as it was not uploaded here itself in the forum. So just considered your sample data in post#4.
I have used a formula in B2 on Summary sheet.

In B2

Please Login or Register  to view this content.

and then copy across and down.
You can increase the sheets with dates but also you need to list the sheet names in row1 and you can copy the same formula across to get the desired values.
You can adjust the range used in the formula as per your requirement.
See if this helps.

[rcm]

Please Login or Register  to view this content.

[oeldere]

and an example of the pivot table.

See the attached file.

[rcm]

this code mimics the color pasted on the days as per your sample

Please Login or Register  to view this content.

[ostego]

I didn't download your worksheet as it was not uploaded here itself in the forum. So just considered your sample data in post#4.
I have used a formula in B2 on Summary sheet.

In B2

Please Login or Register  to view this content.

and then copy across and down.
You can increase the sheets with dates but also you need to list the sheet names in row1 and you can copy the same formula across to get the desired values.
You can adjust the range used in the formula as per your requirement.
See if this helps.

Please Login or Register  to view this content.

Both of your methods work fantastically for what I am trying to do!

Thanks a lot This forum is such a life/time-saver!!

Cheers guys!

[sktneer]

You're welcome. Thanks for the feedback and rep.

[rcm]

You're welcome from my side too. Glad I could be of help...