Formula to automatically copy select row data across multiple columns into new sheet

Hi all

I have tried to do this own but I am in deseperate need of helping and I cant get it done so will be eternally grateful if someone can help me with this.

I have attached a spreadsheet to explain the formula/s I am looking for - the attachment has all dummy data and nothing personal without the use of MACROS.


So I have two sheets:

Analysis sheet: this sheet is where all the order data is held. We have loads of columns as want to hold all the data across ONE row so we can analyse the data efficiently and effectively using a pivot table and would like to keep the format.

On this sheet, to enter individual product sales data we have dedicated multiple columns for each product as you will see. For example, each product we record will have 5 seperate columns to record each products:- product code, size, unit cost per sqm, quantity and total cost.

Purchase Order sheet: This is where we want the formula to work. Since ever order on the analysis sheet is one one row. Whenever a new a order is recorded on the analysis sheet, using the ORDER-PO column (Purchase Order Number), we want to automatically add the respective rows to the Purchase order sheet without having to copy and paste or do anything.

The only difference though is we want the formula to check how many products have been entered on the analysis sheet and then seperately record each product entry on seperate rows on the purchase order sheet.

The attachment I have attached demonstrates what I mean if my explanation is a little unclear.


Then once those all those product data have been copied over into the Purchase Order spreadsheet, in order to not duplicate totals I would also want the last 4 columns on the purchase order spreadsheet to only show the data on the last line of the last product. The reason for this is that when I run a pivot table I dont want the the totals or notes to apply to every single line as it can screw up our calculations and data.

I am truly stuck and if someone can help me I would be super grateful.

Many thanks.

Apologies I am using Microsoft Office 2019. I have updated my profile.

Hey AliGW,

Id like to see if someone can has a formula that would work because when I have vba theres always bugs with the spreadsheet afterwards.

So if we can keep on here and see if anyone can do it through formulas I would greatly appreciate it.

Many thanks

You will need VBA for this so you need think carefully how you want the VBA to be "triggered": probably using a button to start a macro.

And Is there a Purchase Order sheet for each order or are all orders on one sheet?

as a starter set of questions ....!

Hey John and Ali,

So apologies just to clarify.

So what I meant by moved.

Essentially if you look at the example spreadsheet that I have attached at the top of the post - I mean everytime on the analysis spreadsheet an order would be recorded, the purchase order sheet would through a formula add that particular order to the Purchase Order sheet. So in essence the purchase order sheet everytime a new Sales order is added on a row on the analysis sheet, would automatically add that order to the purchase order sheet but break it out over multiple rows if there is more than one item being sold per order.

John to answer your question - the trigger really is everytime a new row is added to the Analysis sheet with its own unique purchase order number - the purchase order sheet (which does exist) would lookup and copy that data into the purchase order sheet.

Hope that answers your question.

If you look at the sample excel sheet at the top it will give you exactly an idea of how it want it work and how I would like the data to be displayed on the purchase order sheet once it carries the data over the analysis sheet.

Any other questions please feel free to ask.

Kind Regards

Hi John

many thanks for this - and Ive tried to answer as best as I can below:

a. The Trigger: is any new row/purchase order number added to the analysis spreadsheet.
b. In terms of the second point - couldnt we add a formula which essentially seperates out all the products into seperate rows if there are text on them and have a formula in the total column which only sums all the individual product totals on the last line of the purchase order?
c. In terms of changes to installation date - wouldn't the formula allow the data to be linked by the purchase order number - so if anything changes in the analysis sheet that the data will also change in the Purchase order sheet similar to like a vlookup.

Many thanks again

If the totals pose a problem then Im not too bothered about that but if everytime a row is added to the Analysis spreadsheet the Purchase Order sheet is linked to it and has that entered there seperating each product row by row but still linked to the original purchase order number and the data is continued to be linked to the Analysis sheet, so if there any changes to the data in the Analysis sheet it will change the data in the Purchase Order Sheet that is key.

Hope this elaboratesit further.

Thanks John for the message.

Is there anyone else who can help me with this - I am helping out a friend who runs a small business which he's taken over from his father who never used IT or recorded his sales digitally before so this will really mean alot of someone can help with this.

Many thanks

It is not a matter of helping you (or your friend): it is the practicality. The whole approach is wrong, Starting with "Analysis" which most people would describe a the last part in a system to derive "Purchas Orders" is the proverbial "cart before the horse".

Before you go any further, have a search on the web to see if there any free suitable Excel templates for your friend's business.

Hi John Yes I understand your point. I mentioned the point around my friend because I know alot more around Excel than him and have gone through countless excel templates - which is why we decided on this spreadsheet.

Its basically nearly there all we need is this last bit where we can connect the order numbers on both sheets and condense the item data across many fields to lesser columns on the Purchase Order sheet.

Again anyone else out there who can help me with this.

Kind Regards

Glen you truly are a superstar this is fantastic.

So Entries from R to U in the Purchase Order sheet go as follows.

R (PAYMENT TOTAL ORDER COST) - That basically adds all the total product costs in column Q and only shows the total on the last line for that purchase order number. So for example, on the Purchase Order sheet - R would only show a value on the last purchase order row which in the sample spreadsheet is row 6. Could you do a formula for that?


S (Customer Paid) - again like the R - this would pull the data in from column BK which is named the same - basically bringing over the amount the customer has paid and only present it on the last row of the purchase order number again which on the PURCHASE ORDER sheet would be row 6.

T (Payment Oustanding) - need a formula which would subtract payment total order cost (column R) from payment - customer paid (column S) and then only show the number/result on the last row for that particular purchase order number which would be row 6 in this case on the example.

U (Payment left) - would like to have another column if possible with a formula for this column I wanted to have a formula which would basically write out text which would indicate that if there was still money still owed by the customer (payment total order cost - payment-customer paid) it would mark paid not paid - again on the last line of the for the purchase order number which would be row 6 on the spreadsheet.


Again many thanks for this Glenn - if you can help with those last columns I will be playing around with it over the next few days and let you know how I get on.

Many thanks Again.

Kind Regards

Hi

My apologies I didnt know I couldnt do that.

I have added the link here for the post on the other forum.

https://www.mrexcel.com/board/thread.../#post-5717276

Hope that resolves it.


Kind Regards

Hi

I have read through it and thanks for making me aware.

Can I still get help on this as some parts are still outstanding thanks.

You are an absolute Legend Glenn.

Apologies again - didn't know the rules previously but will make sure I will only post here in the future.

Many thanks again.