If dates match, then insert data from pivot table

[Chookz]

I have a Summary table in worksheet 1 and a pivot table in worksheet 2.
A shortened version of the summary table looks like this (used / to seperate columns):
Date / Time / TRIMPS
2/05/11 / xx:x / ?
3/05/11 / xx:x / ?
4/05/11 / xx:x / ?
5/05/11 / xx:x / ?

My pivot table has:
Date / Sum of TRIMPS
2-May / 363
3-May / 311
4-May / 243
5-May / 345

In my summary table I want to enter a formula where the TRIMP values from my pivot table will be inserted into the TRIMPS column of my summary chart for each corresponding day (from worksheet 2 to worksheet 1). Example if this date matches that date, then insert the corresponding TRIMP value. Any help is greatly appreciated, and please remember im a beginner with excel so please use as much detail as you can. I hope this makes sense as its difficult to explain.
Thanks in advance

 

[Peter_SSs]

Welcome tp the MrExcel board!

A VLOOKUP formula should do what you want.

You may need to adjust the sheet name to exactly match yours and the ranges may need slight adjustment. However, assuming the tables/data start in cell A1 of each sheet, try this in cell C2 of worksheet 1 and copy it down the column.

Post back with more details if it doesn't do what you want.

=VLOOKUP(A2,'worksheet 2'!$A$2:$B$5,2,0)

 

[Chookz]

Hi Peter,
Thanks for the welcome to the forum. And thanks for your help with the question. The second worksheet is called Pivot1 and the date data in the pivot table runs from L3 to L97, and TRIMPS data from M3 to M97 (But the pivot table will expand past row 97 as I update it every week). Not knowing exactly what the numbers in your formula are for, I tried to plug my cell ranges into your formula and got this:
=VLOOKUP(A11,'Pivot1'!$L$3:$M$97,2,0)
However this comes up with #N/A.
Any thoughts? And would you be able to briefly explain what the characters in your forumla do i.e. !, $, 2, 0 as im a beginner at excel and this will help me learn.
Also does it matter that the date in my summary chart is formatted as dd/mm/yyyy and in the pivot chart its dd-mmm?
Thanks again

 

[Peter_SSs]

Not knowing exactly what the numbers in your formula are for, I tried to plug my cell ranges into your formula and got this:
=VLOOKUP(A11,'Pivot1'!$L$3:$M$97,2,0)
However this comes up with #N/A.
Any thoughts?

That looks like a good start (apart from the #N/A result )

1. Please confirm that A11 conatains a date.
- What date?
- In a vacant cell on that sheet put this formula and advise the result.
=ISNUMBER(A11)

2. Can you confirm that the date from A11 actually exists in column L of sheet 'Pivot1' (format of date doesn't matter)?

3. Assuming Q2 is confirmed,
- what cell is that date in?
- Assuming the answer to the previous point was, say, L10 then in a vacant cell in sheet Pivot1 put this formula and advise the result
=ISNUMBER(L10)

The second worksheet is called Pivot1 and the date data in the pivot table runs from L3 to L97, and TRIMPS data from M3 to M97 (But the pivot table will expand past row 97 as I update it every week).

Just change the 97 in your formula to some number bigger than you are ever likely to get in your Pivot Table (say 1000 or 5000)

And would you be able to briefly explain what the characters in your forumla do i.e. !, $, 2, 0 as im a beginner at excel and this will help me learn.

! is used to signify what comes before ('Pivot1' is a worksheet name.

$ makes the cell address 'absolute' so that if the formula is copied to another row/column the cell address does not change. Look in the Help or online for 'absolute address' and/or 'relative address'

2 says to return the value from column 2 of the lookup range (that is from column M in this case and the 0 says to find an exact match for the lookup date in the first column. Look in the Help for VLOOKUP where all that is explained fairly well I recollect.

Also does it matter that the date in my summary chart is formatted as dd/mm/yyyy and in the pivot chart its dd-mmm?

It shouldn't provided the underlying dates are the same. My earlier questions should shed some light on that.

 

[Chookz]

Hey Peter, thanks again for your detailed response. Your formula makes much more sense to me now!
In answer to your questions:
1. A11 does contain a date - 2/05/2011
=ISNUMBER(A11) - TRUE

2. Yes the date from A11 (2/05/2011) exists in column L of sheet Pivot1 (2-May)

3. 2-May is in cell L91 of sheet Pivot1,
However =ISNUMBER(L91) - FALSE

Is the FALSE my problem? Do you know of how I can fix it?

My Pivot Table is arranged as follows (x = other data):
Years / Months / Date / Sum of TRIMP / x / x
2-May / 363

2-May being in the Date column in cell L91 and 363 being in the sum of TRIMP column in cell M91 (years/months/date being how ive grouped my date).
Many Thanks

 

[Peter_SSs]

2-May is in cell L91 of sheet Pivot1,
However =ISNUMBER(L91) - FALSE

Is the FALSE my problem? Do you know of how I can fix it?

Yes, The FALSE is your problem, we are trying to match a date (number) with a text value.

Since the text value is part of your Pivot Table I wouldn't fiddle with that. Try one of these formulas instead.

If the month names in the Pivot Table are abbreviated to 3 letters (hard to tell with May ).
=VLOOKUP(TEXT(A11,"d-mmm"),Pivot1!$L$3:$M$1000,2,0)

If the month names in the Pivot Table are in full, add an extra 'm'.
=VLOOKUP(TEXT(A11,"d-mmmm"),Pivot1!$L$3:$M$1000,2,0)

 

[Chookz]

Yes, The FALSE is your problem, we are trying to match a date (number) with a text value.

Since the text value is part of your Pivot Table I wouldn't fiddle with that. Try one of these formulas instead.

If the month names in the Pivot Table are abbreviated to 3 letters (hard to tell with May ).
=VLOOKUP(TEXT(A11,"d-mmm"),Pivot1!$L$3:$M$1000,2,0)

You are a genious, this formula worked! Thanks very much for your help. Actually one more problem that im sure you'll be able to help me with (pivot tables and charts are a big step up from what im used to)!
Is there a way to add a data set to my pivot table without it appearing in the pivot chart that i have setup from that table? Whenever I add another data field to the table it automatically screws up my graph

 

[Peter_SSs]

Actually Pivot Tables and Pivot Charts are not strong points of mine. Hopefully somebody else will step in on that one.

Since it is a different question you could start a new thread for that. If you do so it would be best if you reply here again with a link to your new thread so we don't accidentally end up with 2 threads going on the same topic.