IF formula & Lookup Formula Help to create semi automatic packing slip

[Gemma Telfer]

Hi 

I am trying to create a packing slip tab in my excel workbook which when I enter the unique order number will then populate the packing slip lines. I have managed to so this for the customer name and pick up location boxes but I cannot for the life of me figure out how to make it populate the order. 

The data is on the list of order tabs which lets me know what exactly someone has ordered but not sure how to get this information onto the packing slip automatically..

Any help much appreciated.

[#2]

@Gemma Telfer 

See the attached version.

[#3]

Hi @Gemma Telfer 

I would propose a Power Query solution, it's easy to implement, very straight forward and should work with all Excel versions from 2016 on.

Attached please find my solution. All you need to do is to trigger a refresh once you have changed the order reference number in your packing slip:

(the first time, you would also need to click on the "enable content" button)

To implement this solution, the list of orders and the products list have been converted into formatted tables (menu "Home | Format as table".

And for the cell with the order reference number, I have defined a name:

This way, everything can be loaded and combined in Power Query, the result is loaded directly in the packing slip sheet.

Hope this helps.

Kind regards,

Martin

[#4]

@Gemma Telfer 

An alternative which works with formulas available in Excel 2013 is in the attached file. The formulas for ID and Quantity are arrayformulas and have to be entered with ctrl+shift+enter if one doesn't work with Office 365 or Excel 2021 or Excel for the web.

[#5]

Hi @HansVogelaar @Martin_Weiss @OliverScheurich thank you all very much for you assistance and time to help me with this. It is much appreciated!🙂

[#6]

@Martin_Weiss thankyou that works perfectly and is so simple to use. This will make my life so much easier.