Solved

Sum of quantities associated with changing duplcate values in a remote Excel Spreadsheet

Posted on 2015-02-22
8
30 Views
Last Modified: 2015-03-22
I need a result list that contains the sum of quantities associated with changing duplcate values in a remote Excel Spreadsheet.
Source data changes frequently as data is pulled from a large database.  
Names in the list are a subset of a larger database.  
The source record count will change frequently.
namedata.xlsmnametotals.xlsm
0
Comment
Question by:AMixMaster
8 Comments
 
LVL 11

Expert Comment

by:Wilder1626
ID: 40624956
Hi AMixMaster

I would simply go with a Pivot Table.

It will also be easy to adjust everyday.
namedata-Pivot-table.xlsm
0
 

Author Comment

by:AMixMaster
ID: 40624959
The result must be in a seperate workbook.
I want to avoid a pivot table and use linking formulas.
The sheet is distributed to many users and a remote pivot table does not always work.
0
 
LVL 11

Expert Comment

by:Wilder1626
ID: 40624967
Will you always like to 1 single path to a specific workbook or multiple path and then merge all together into a master workbook before you create your final summary?
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:AMixMaster
ID: 40625058
single path, master workbook is open when data is read and closed after breaking links and copy paste values.
master workbook is not editable as it is rewritten frequently by the database program.
i linked a large range that includes all the source data and placed it on a hidden sheet with the pivot table.  
it works for now but liinking the report output to the pivot table data does not guarantee that the cell references will be the same whenever the data changes.
I thought of using index, sumif or frequency but can't resolve the many to many relasionship without the pivot table.  
it;s complicated.....
0
 
LVL 12

Expert Comment

by:FarWest
ID: 40625111
There is a way I think to use SQL compact edition to process you data and get results from source using ordinary SQL statement and save it to target. It will be easer to implement

Open in new window

0
 

Accepted Solution

by:
AMixMaster earned 0 total points
ID: 40627217
A collegue provided this and it solved the problem:
The solution uses "INDEX" in a different formula in each of the 3 columns.

{=IFERROR(VLOOKUP((INDEX('[Workbook.xlsx]worksheet'!$B$1:$P$50,SMALL(IF('[Workbook.xlsx]worksheet'!$I$1:$I$50="crteria one",ROW('[Workbook.xlsx]worksheet'!$H$1:$H$50)),ROW(1:1)),1))&" criteria two",'[Workbook.xlsx]worksheet'!$BR$5:$BU$500,4,FALSE),"")}
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 40680771
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

706 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now