?
Solved

How to move data from one sheet to another and place it with the appropriate customer.

Posted on 2012-08-11
8
Medium Priority
?
233 Views
Last Modified: 2013-04-07
I have two spreadsheets, one has a customer name, invoice number, quantity, amount per, and extension.  There can be 1+ invoices for each customer.  I will call this the detail sheet.

The second spreadsheet has a calculation that may change the amount per charged on the invoices based on the monthly quantity. This I will call the summary sheet.

 When this new amount per is calculated it must be put back into the first spreadsheet. I am happy to create a new amount per for each entry, that will allow me to recalculate the total.

The first spreadsheet only has one row for each customer because it is based on the total amount sold to that customer.  But the second spreadsheet has each individual invoice so their may be any number of invoices for a customer.  

Therefore I need to match the new amount per to each and every invoice for said customer.  So how do I move the amount per to each invoice for that customer name.So customer name will be the same on each sheet.  The original amount per on the detail sheet will stay the same but a new one will be created for each of the invoices, it should be new amount per.
0
Comment
Question by:SharonStys
  • 4
  • 3
8 Comments
 
LVL 43

Assisted Solution

by:Saqib Husain, Syed
Saqib Husain, Syed earned 996 total points
ID: 38284774
Can you upload a fake sample file to explain your question more clearly?
0
 
LVL 1

Author Comment

by:SharonStys
ID: 38284798
One page has the Summary information which has the new amount per field called lesser rate.

The second page is the original file.   To each record in the file, by name we will have a new column called lesser rate to recalc each invoice based on the lesser rate field.  The lesser rate comes from the summary page where we selected that rate based on the total each customer purchased.  The rate would be different if you did it by each individual purchase so we need the subtotal to get that number.

I hope this helps.  Thank you
Expert-Sample-File.xlsx
0
 
LVL 43

Assisted Solution

by:Saqib Husain, Syed
Saqib Husain, Syed earned 996 total points
ID: 38285152
Enter this formula in B2 and then copy it down and across.

=SUMIF(Original!$C:$C,Summary!$A2,Original!E:E)
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 1

Author Comment

by:SharonStys
ID: 38285217
Can you clarify what this formula is doing please?  I understand you are saying if the name in original is the same as the name in summary but then i am lost because it is the lesser amount which is in I2 in Summary that we would want to put in K2.  I don't understand the A2 and the E's.  

Also you say enter in B2 and copy down and across, do you mean the entire form?

Sorry  I need and want to clearly understand this.
0
 
LVL 22

Accepted Solution

by:
Dreamboat earned 504 total points
ID: 38285610
Just enter it in B2 and copy down. Not across. That provides your totals unit purchased. Looks to me like that's all you need.

Sumif:
Original C:C = your units
Summary A2 = the customers name to sum
Original E:E = the column to sum

Any points to ssaqibh, please.
0
 
LVL 1

Author Comment

by:SharonStys
ID: 38285700
Original c:C= name in invoice record
Summary A2 =name in the summary of invoices file
Original E:E = is the quantity on the invoice it is not the correct field

            The Summary I (Lesser Rate) is the number we want to return to each invoice
             record for the specific name.  We would put that number in column K with a title
             Lesser Rate.  

So we are using Summary page to create the Lesser Amount, then we need to move that Lesser Amount to Original in K for the matching name.

I thinK we are very close but I do not know how to edit your formula to make this change.

I am excited to have your assistance and happy to apply all points when I can make it work.

Thank you, Sharon
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 39055562
I am very sorry I had not seen any of the posts after my last comment. I must have been busy elsewhere at that time.

I wonder if you still need assistance. If yes then just say so and I shall be too glad to continue.

Here is your file with my formula modified a bit to suit.
Copy-of-Expert-Sample-File-1.xlsx
0
 
LVL 1

Author Comment

by:SharonStys
ID: 39056092
Thank you I found the problem with the calculation.  It seems the person for whom I created the calculations inadvertantly changed the calculation from a < sign to a > sign and caused the problem.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone 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

Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

840 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