98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View ReviewsForum home » Delegate support and help forum » Microsoft Excel Training and help » Formulas
Formulas
Resolved · Urgent Priority · Version 2003
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
Training information:
See also:
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Closing Multiple Open Worksheets At OnceWhen multiple Excel worksheets are opening, rather than performing a File > Close menu option multiple times, hold down the the SHIFT key and select the File > Close All menu option. |