amelie has attended:
Excel Intermediate course
Formulas
I need to add many sales orders togethers to have 1 workbook with the total sales. ex: 5 black shirts in size 10, 3 white pants in red,....Is there a way to add all the quantities to have my total sales sheet without copy pasting the formula =(sheet 1+sheet 2)? Also, in order to manage our stock (pieces in stock), I need to substract the pieces sold. So it is PIECES BOUGHT - PIECES SOLD (adding the sales order of each cleitn) = FREE TO SELL
STOCK. I would like to avoid copy pasting formulas for hours (many human errors).
Thank you!
RE: Formulas
Hi Amelie
Thank you for your question
If the data that you are seeking to bring together is laid out identically on the different worksheets, then your best approach is to use the Consolidate Function.
In the worksheet where the totals are to go, click on the top left hand cell of the target range. Then click on data and consolidate
Click inside the reference text box and select the first sheet with data and select the range of cells containing the data to be added and click ADD. Then select the next sheet and repeat the process. The range should now be auatomatically identified. Repeat until all the sheets have been added
Then tick the create links to source data tick box. This will update your total field if one of the individual sheets is changed.
Then click OK to create the summary
If this doesn't work please let me know so we can explore other approaches.
Regards
Stephen
