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

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.
LVL 1
###### Who is Participating?

East Coast ManagerCommented:
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:
Summary A2 = the customers name to sum
Original E:E = the column to sum

0

EngineerCommented:
Can you upload a fake sample file to explain your question more clearly?
0

Author Commented:
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

EngineerCommented:
Enter this formula in B2 and then copy it down and across.

=SUMIF(Original!\$C:\$C,Summary!\$A2,Original!E:E)
0

Author Commented:
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

Author Commented:
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

EngineerCommented:
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

Author Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.