Solved

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

Posted on 2015-02-22
8
32 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Sum Based On Criteriya 7 19
Problem to With line 4 39
ms office troubleshooting for users 8 35
IF ELSE Statement in Excel Macro VBA 16 30
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

911 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

16 Experts available now in Live!

Get 1:1 Help Now