Bring info from one worksheet to another - sum up volume

Resolved · High Priority · Version 365

Mon 21 Aug 2023, 12:24 Delegate 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:51 Trainer 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:36 Delegate 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:46 Delegate 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:27 Trainer 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 courses

	98.7 % recommend us
	More live stats...

Training information:

Excel tip:

Creating custom lists

In Excel if you type in January in a cell, you can then copy this cell to replicate Febraury, MArch, April etc.

This list has come from Tools- options and Custom lists.

Therefore to save time and create your own list you can click on New (in Tools and custom list tab) and type out the lsit that you want copied quickly.

All you have to do is then type in the 1st word and you will be able to copy the rest of the list quickly.

View all Excel hints and tips