Solved

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

Posted on 2015-02-22
8
36 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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 46

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
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…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

832 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