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
SharonStysAsked:
Who is Participating?
 
Anne TroyConnect With a Mentor 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:
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
 
Saqib Husain, SyedConnect With a Mentor EngineerCommented:
Can you upload a fake sample file to explain your question more clearly?
0
 
SharonStysAuthor 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
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.

 
Saqib Husain, SyedConnect With a Mentor EngineerCommented:
Enter this formula in B2 and then copy it down and across.

=SUMIF(Original!$C:$C,Summary!$A2,Original!E:E)
0
 
SharonStysAuthor 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
 
SharonStysAuthor 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
 
Saqib Husain, SyedEngineerCommented:
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
 
SharonStysAuthor 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.

All Courses

From novice to tech pro — start learning today.