Excel 2016 - Creating an automatic invoice, PO and Packing List from manual entry.

Hi! I'm new here and this is my first post. Hope you can help!

I just landed my first job after finishing my education and one of my first tasks was to try and speed up the paper work processes in the office. We function as a sales/service/contractor for parts and systems in the energy sector. Whenever a client needs something, a parts list pops up in my email chain. This might be a .pdf, .jpg or just plain text, so it is manually entered into a quote calculation sheet in MS excel 2016. This is fine and works great. 

However there are four more excel sheets that needs to be filled out in order to have everything properly arranged and billed. The customer gets a quote, an invoice and also a packing slip. Additionally we send a purchase order to the supplier. 

What i would like to do is to somehow make excel auto fill these documents so that they also dont have to be typed up manually. They all contain the same information from the first document (quote calculation) and the text and numbers entered into these cells (manually) needs to be copied and pasted into different cells in different sheets, so that the four new documents gets created automatically. 

I had a look around and i found a video showing how to combine two different spreadsheets into one in the same workbook. This is generally what i need to do. It showed how to use the "data from other source" and "microsoft query", which i think is the key here. But i don't know!? I'm relatively new to this and would definitely like to learn more.

Anyone know if and how this is possible?

Thanks in advance.

- Martin

"Im using excel on Mac as we speak, but when my company laptop arrives, i'll be on Windows"

|
Answer
Answer

Hi Martin,

Based on your description, I suggest using cell reference. Here is an example:Quote calculation sheet has the following data.

1. In a sheet that needs data from the quote calculation sheet, select a cell > press = key in your keyboard > select the cell that has the desired data in the quote calculation sheet > press return.

2. Repeat step 1 for other cells.

If the above can't meet your requirements, please share with us a screenshot about the quote calculation sheet with fake data and a screenshot of the expected result for other sheets.

Thanks,

Tina

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Answer
Answer

Hi Tina!

The cell reference thing worked perfectly. I solved the situation by moving all the sheets to the Quote Calculation book. Somehow this was not stated in any self-help-guide online. I discovered it by accident. we're using cell reference for now, and it is a smart function! (i used the pull down-column thing and it understood what i wanted cell-wise).

Great stuff, 

Thanks!

- Martin

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

 
 

Question Info


Last updated May 22, 2018 Views 535 Applies to: