Pulling Data from Two Other Sheets to Calculate Cost of Goods Sold

[madmoneymike5]

My question is rather complicated to explain via typing it out. If I could do a bit of show-and-tell then the answer I receive will likely I be more accurate and result in less back-and-forth trying to clarify things. So it is in that spirit that I took the time to create a short video that does just that, and I ask that you watch it to get a better understanding of what it is I'm trying to accomplish.

https://www.youtube.com/watch?v=G995_5QzNcE

The short text version: I need to search through a sheet with sales data on it and cross reference that to a sheet with costs of goods, then tabulate those together to determine how much (in terms of cost) inventory was sold in the sheet with sales data. There are some nuances though because the sales data doesn't have just one line per order; it sometimes has multiple lines per order that are not inventory related. See the video for a better explanation.

Thanks!

Mike


P.S. I promise it's nothing obscene, spam, or anything of the like. It truly is a "video question."

 

[sandy666]

here is easier way:

Desposit COGS Example

 

[madmoneymike5]

Thanks Sandy.

I see it working in the online version but when I download it, your table goes away. I suspect that's because until today I had never heard of PowerQuery and of course, don't have it. Is this a free or paid add-in to Excel? I can't seem to figure out how to download it.

Thanks,

Mike

 

[sandy666]

You've PowerQuery, it's Get&Transform.
Look at your ribbon

 

[madmoneymike5]

Oh, sure do!

I've never used it. Would you mind walking me through the steps to create this query? I don't know where to even begin...

 

[sandy666]

• Getting Started with Get & Transform in Excel
• Microsoft Power Query for Excel Help
• Excel Power Query (Get & Transform) Tutorial for Beginners

 

[sandy666]

First you need to use Show Queries (or something similar - maybe Queries and Connections) which allow you to see Workbook Queries (Tables loaded into PowerQuery from Excel sheet(s)