bring info one worksheet

Public Schedule Face-to-Face & Virtual Instructor-Led Training - View dates & book

• Home
• Courses
• Promotions
• Schedule
• Formats
• Our Clients

Forum home » Delegate support and help forum » » 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

Manuela has attended:

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

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

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

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

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

Training information:

 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 What does 'Resolved' mean? Any suggestions, questions or comments? Please post in the Improve the forum thread.

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

Server loaded in 0.08 secs.