[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Update tables with July Data

Posted on 2014-08-18
7
Medium Priority
?
80 Views
Last Modified: 2014-08-20
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
0
Comment
Question by:robmarr700
  • 2
  • 2
  • 2
6 Comments
 
LVL 54

Expert Comment

by:Rgonzo1971
ID: 40267654
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
 

Author Comment

by:robmarr700
ID: 40267682
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
 
LVL 54

Expert Comment

by:Rgonzo1971
ID: 40267687
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40268358
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
 
LVL 27

Accepted Solution

by:
Glenn Ray earned 2000 total points
ID: 40268419
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
 

Author Comment

by:robmarr700
ID: 40272037
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

825 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question