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

hionman

New Member
Joined
Nov 11, 2014
Messages
10
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
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,507
Office Version
  1. 365
Platform
  1. Windows
Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Formula to automatically copy select row data across multiple columns into new sheet
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.
 

hionman

New Member
Joined
Nov 11, 2014
Messages
10
Hi,

Apologies didnt know you cant do that in forums.

Please find the link below

 

Forum statistics

Threads
1,141,570
Messages
5,707,141
Members
421,493
Latest member
Tusharrm

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top