Vlookup or Getpivotdata or ?

[Guest]

I'm having a huge amount of difficulty trying to do the following:

I have a pivot table showing business YTD by customer number however the
main data is by line item (so each customer number is listed 50, 60, more
times per table). The order number is also repeated based on the number of
line items.

I also have another set of data with a pivot table showing last years data
and out to the side in dead space the projected amount of sales by customer
id.

What I am trying to do is take the current data (from the pivot by the
customer number and business segment, yes the dollar values by customer
number are broken down by product line (3 of them) -- and update the dead
space in the original last years table to the right of the typed in 'planned'
sales.

Instead of having someone manually go through each set of data (30 +
spreadsheets) and see the customer number, business done in each segment and
go back in and update the actuals next to the planned data. I want it to be
able to find by customer number from actuals to pull in the updated amount
itno the original spreadsheet that contains last years numbers (and of course
to the right of that is projected/planned business by customer number and to
the right of that -so that it can all be in one place - the actuals).

Please let me know if this makes sense.
- 2 seperate sets of data
(last years with planned to the right)
(this years YTD)
- want to be able to pull current from this years YTD and place into far
right colums by segment into the original by utilizing customer number but
also being able to identify by product line where to place the amounts.

Thanks,
CE

 

[Guest]

Hey, I could help you out if I understood the question a little better. Try
to explain the data sets by Column headings and how to find the data you need
and where exactly to put that data.

 

[Guest]

I have pulled in some data that has customer id, customer name, ship to
location, line number, line value, product codes, product segment, order
date, rep code, etc.

I have this for last years data and this years data in seperate spreadsheets
and the data is on tab 2, pivot on tab 1. I've summarized the data by
customer id, location and value of orders in total by segment for both last
years and this years.

Last years information (outside the parameters of the pivot table) I've
added 'projected sales' columns (for the 3 product segments) and by
location/customer id, sales has placed values in those cells relative to the
customer id its out next to.

Now, with current data run from the system (access via ODBC links) I've run
YTD current and pivoted the data in the same layout. I need to be able to
update the last years (original) spreadsheet out to the side of the
'projected sales' columns to show the actuals. We can then say % to plan of
YTD data.

Does that make sense? But I'm not figured out how to get the data pulled
over like that.

 

[Guest]

Ok, so what you are saying is that you created a field of projected values
manually next to last years pivot table. Now you have this years pivot table
and want to manually input the actuals in to the column next to the projected
values or next to the projected values?

If I get you correctly, you should be able to write GETPIVOTDATA formulas
next to the projections... this way as the actuals get updated, the
GETPIVOTDATA will be automatically updated. Do you have experience in
GETPIVOTDATA? I see you understand ODBC so you must have some good knowledge
of excel... other then that, and without being able to see a sample sheet, I
can't think of anything else. I use multiple consolidated ranges to display
years and years of data from separate spreadsheets but I'm not sure if that
will do what you need.

 

[Guest]

Yes, that is exactly what I'm trying to do. I've never used getpivotdata, but
I tried a few times yesterday but not successfully. The two spreadsheets are
identical in layout structure but with last year vs. YTD current data. If I
needed to do the current pulled into the previous year and have that data
dump off beside 'projections' how can I do that and make it associate the
data by customer number and product line?

Thanks,