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