Pulling Purchase price from a master sheet

yusiboyz

Hey All,

I have a master sheet where I input items that I purchased and it's price. To identify a product I use SKU's for each Product.

Now every two weeks I pull my sales and import it to a new smart sheet. The I copy the sku and go into my master sheet and find it there and look at how much I paid for it, then I got back to the import and paste the purchase price.

Is there a way to have this automated? Can it go to the master sheet and look for the SKU then see how much I paid for it and come back and paste it?

CleanShot 2022-10-05 at 10.14.15.png

Uploaded Oct 5, 2022286.53 KB

Michael Culley

That would be =Index({price per item},match([sku]@row,{sku from other sheet},0))

Michael Culley

So after you type =index( you then click on the "reference another sheet", go to your purchase orders sheet and select the column Price per item. Then type Match([sku]@row, then click "reference another sheet" and select the SKU column from the purchase order sheet, then finish the formula with a ,0))

yusiboyz

Michael Culley

Oct 5, 2022

So after you type =index( you then click on the "reference another sheet", go to your purchase orders sheet and select the column Price per item. Then type Match([sku]@row, then click "reference another sheet" and select the SKU column from the purchase order sheet, then finish the formula with a ,0))

That worked Michael! Thank you! Now is there a way to automate it so that it does it for every new sheet?

Michael Culley

Are you keeping the older sheets or just pasting the new data over the old sheet?

yusiboyz

I am keeping the old sheets. Every two weeks, I create a new sheet for the new sales.

Michael Culley

You would need to do new references for those sheets. Or edit the reference and point to the new sheet. Depends on what you're trying to accomplish.

yusiboyz

Michael Culley

Oct 5, 2022

You would need to do new references for those sheets. Or edit the reference and point to the new sheet. Depends on what you're trying to accomplish.

Can I create a template with this formula basically? I tried copying the forumla that worked in one sheet and into another sheet but that didn't work.

So basically every new sheet I have to redo the steps you mention here:

So after you type =index( you then click on the "reference another sheet", go to your purchase orders sheet and select the column Price per item. Then type Match([sku]@row, then click "reference another sheet" and select the SKU column from the purchase order sheet, then finish the formula with a ,0))

Michael Culley

It would be helpful to see screenshots so I have a better idea what you’re doing. Then maybe I can suggest something for ya.