Solved

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

Posted on 2015-02-22
8
37 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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

839 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