Link to home
Start Free TrialLog in
Avatar of robmarr700
robmarr700

asked on

Update tables with July Data

Questions aimed at Glenn Ray,

Attached is my most recent data sheet with July data added.

I am having difficulties adding the data to the three relevant tables/pivots.

Could you take me through the steps I need to take to do this for now and future months and perhaps suggest ways in which this process could become more automated.

regards
Rob
EE-Master-YTD-3--3---Autosaved-.xlsx
Avatar of Rgonzo1971
Rgonzo1971

Hi,

In the Sheet FY2014 in cell B21782 you have to insert this formula ( to have the right Customer name) the wrong names where filtered out in the Pivot table

=VLOOKUP(A21782,'Customer Names'!A:B,2,FALSE)

And fill down  to the end of the list

and repeat every month by copying the formula in the cell in Col B if the Cell in the same row in Col A is not empty

Regards
Avatar of robmarr700

ASKER

Apologies if I was not clear,

I have directed this question at Glenn as he has been working through this project with me from the outset.

I have raised it as a new question as it is separate to what we have covered before.

Rob
Or you could use in the whole colmn B except B1

this formula from B2 fill down until the end of your list

=IFERROR(VLOOKUP(A2,'Customer Names'!A:B,2,FALSE),"")

EDIT

Better

=IF(A1<>"",VLOOKUP(A2,'Customer Names'!A:B,2,FALSE),"")


Regards
EE-Master-YTDv1-.xlsx
Rob,

The reason for inserting the VLOOKUP function in column B was to ensure that the customer names used were consistent for the "Pivot-Report" and related "Member T.O YTD NEW" worksheets.

That should be easy enough to do and while Rgonzo1971's formulas above would clean up the results, you DO need to see if there are new customer numbers (i.e., returning #N/A! results in the VLOOKUP) so the "Customer Names" table can be updated with new names.

As for automating the remaining steps for building the yearly data, I think that's possible.  Here are the general steps I see:
1) After adding the monthly data to FY2014 sheet, insert the VLOOKUP on the Customer Name column, add the Fiscal Month name in column Q, copy down TS-diff and TS-pct formulas in columns R & S.
2) Insert new monthly columns for "Supplier T.O YTD NEW" and copy over formulas from previous month
3) Insert new monthly columns for "Member T.O YTD NEW" and copy over formulas from previous month
4) Refresh PivotTable and format the three new columns for the new month.
5) Update the Supplier names table ("Lookup" sheet) and Customer Names table as needed.

Does that cover it?  :-)  

-Glenn
ASKER CERTIFIED SOLUTION
Avatar of Glenn Ray
Glenn Ray
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Spot on! That's great thanks Glenn.

I'm going to raise a new full parse project today which might interest you.

I'm going to split it in to several small questions to make it easier to work through.

Rob