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
robmarr700Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rgonzo1971Commented:
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
0
robmarr700Author Commented:
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
0
Rgonzo1971Commented:
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
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

Glenn RayExcel VBA DeveloperCommented:
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
0
Glenn RayExcel VBA DeveloperCommented:
In the interim, here's an updated version of your workbook with July data updated.  I've removed the links to the SalesReportingUtility.xlsm workbook and replaced with values.
EE-Master-YTD-3--3---Autosaved-.xlsx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
robmarr700Author Commented:
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.