Share this content
0
5
1985

How do I add values across files

How do I add values across files

Hi,

Adding values across files:
 
I send out blank Excel order forms, with a total of c. 50 product lines, spread over 3 worksheets, to individual clients.
I receive back completed order forms from the clients.
They all order different items.
 
Is there a simple way to add each individual's order to a master file, so I know the total of each product line that I should order from my supplier?

Thanks very much indeed.

Replies

Please login or register to join the discussion.

avatar
By neileg
25th Nov 2011 09:53

Try Consolidate Data in Excel

The task would be easier if you only had one spreadsheet per customer but you may find that the various consolidation approaches my work for you. Look in Excel Help.

Alternatively you could link the spreadsheets in Access.

Which approach works best will depend on the numbers of sheets and having identical positions for products, etc.

Thanks (0)
By Saffuze
25th Nov 2011 10:28

How about an online database

Hi Ruben,

Nelleg has suggested you use one spreadsheet per customer to make your life easier but my understanding is that is what you are doing but you would like to find a way to merge all the individual clients spreadsheets to one to make your order to your suppliers easier.

 

One thought that comes to mind is that you could hold a master file with the first worksheet being your order to your suppliers which looks up (counts) orders from customers from other worksheets.  As an order comes back from your client, you could copy the data from their spreadsheet to worksheets named ClientOrder1, ClientOrder2 etc.  A little time consuming copying the data each time but may work.

 

Another approach could be to use something like Zoho Creator which I have used a little bit and quite like.  In effect you create your spreadsheet order form online and provide a link for your clients to login (after registering) and update their order accordingly.

 

The database could then be as basic or as advanced as you wanted and had time to develop.

 

Anyway, just a couple of quick thoughts.

John

Thanks (0)
avatar
25th Nov 2011 14:39

yes

but it depends on how well your sheets are structured.

Thanks (0)
avatar
14th Dec 2011 16:36

Combine all the worksheets into one, then use a pivot table

 

Assuming each order form has the fields PartNo and QtyOrd in them and are 50 rows deep.

In each worksheet insert a new column CustNo, Then insert a Customer code down the 50 rows using Copy Down.

Assuming you have 20 customers, do this on all 20.

 

Now cut and paste all 20 x 3 worksheets into a new worksheet called Master.  This will end up containing 1000 rows.

 

Create a pivot table from these 1000 rows.  Excel 2007  Insert - Pivot Table - Row Value = PartNo,  Data Value = QtyOrd

 

If you want to see which customers have bought which products, put CustNo in the Row Value after PartNo 

 

Thanks (0)
avatar
16th Dec 2011 16:38

VBA Solution

A simple VBA macro will do this for you. If you want me to knock something up for you that does this (no charge) then send me the spreadsheets to look at. It shouldn't take me more than a couple of hours to turn around.

But if it's an important part of your business then you really should look at getting your customers to order online via a secure site. These days what you're asking for can be done for as little as £400-£500 and looks much more professional than an Excel-based ordering system.

Perhaps go with my VBA solution first and see how it works for you? The code will only be contained in your master sheet so you won't need to send macro-enabled worksheets to your customers, just basic .xls or xlsx files.

If you're interested, get in touch at james at fd4cast dot com.

Thanks (0)