98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel Training and help » Bring info from one worksheet to another - sum up volume
Bring info from one worksheet to another - sum up volume
Resolved · High Priority · Version 365
Mon 21 Aug 2023, 12:24Delegate Manuela said...
Manuela has attended:
Excel Advanced course
Bring info from one worksheet to another - sum up volume
Hi all,
I need to bring the information from one worksheet to another. I need to sum the volume of containers that do not yet have date of arrival confirmed yet. Those cells are still blank. I believe it's the Vlookup formula? I feel a little lost here.
Thanks
Mon 21 Aug 2023, 13:51Trainer Marius said...
RE: Bring info from one worksheet to another - sum up volume
Hello Manuela,
Thank you for your question. Based on the information you've given, a SUMIF function will be able to give you the total volume.
=SUMIF([Book1]Sheet1!$A$2:$A$10,"",[Book1]Sheet1!$B$2:$B$10)
In this example:
* Book1 represents the file with the data
* Sheet1 represents the worksheet with the data
* $A$2:$A$10 represents the column range with the Dates
* "" tests for empty cells in the Dates column (no arrival date)
* $B$2:$B$10 represents the Volumes range
Written correctly, this SUMIF formula will bring across the total volume of non-confirmed arrivals.
I hope this helps.
Kind regards
Marius Barnard
STL
Mon 21 Aug 2023, 14:36Delegate Manuela said...
RE: Bring info from one worksheet to another - sum up volume
Thanks Marius,
I forgot to mention that I also need to select the item code.
Eg. Item X, in transit (no arrival date), sum of volume (quantity)
And then apply the same formula for each item from the same data base.
I hope it's clear?
Many thanks
Tue 22 Aug 2023, 08:46Delegate Manuela said...
RE: Bring info from one worksheet to another - sum up volume
Hello good morning! :)
I will reformulate my question.
I have a spreadsheet that contains shipments of different items, and quantity per item. With different shipment dates. Separated by weeks.
I want to create another spreadsheet that shows the volume (per item) of volume that has not been shipped yet (blank cell). This formula also needs to deduct the quantity every time that I add a date on that line (only from that week onwards).
I hope it's clear?
Many Thanks :)
Tue 22 Aug 2023, 09:27Trainer Marius said...
RE: Bring info from one worksheet to another - sum up volume
Good morning Manuela,
Thank you for posting again. I think the following formula will do what you asked for:
=IF([Book1.xlsx]Sheet1!B2="",XLOOKUP(B2,[Book1.xlsx]Sheet1!$A$2:$A$5,[Book1.xlsx]Sheet1!$C$2:$C$5),"")
In this formula:
* Book1 is the data file
* Sheet1 holds the data
* column A holds the item codes
* column B holds the dates
* column C holds the volumes
This formula first tests if a cell is empty in the Dates column. If so, it will match the item code (the XLOOKUP function does this) and will bring the volume across to the other sheet.
When a date is entered in the original sheet, it will automatically clear the corresponding volume in the other sheet and this will be "subtracted" from the grand total.
I hope this formula helps.
Kind regards
Marius
Tue 29 Aug 2023: Automatically marked as resolved.
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:Creating custom listsIn Excel if you type in January in a cell, you can then copy this cell to replicate Febraury, MArch, April etc. |
