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

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

https://community.smartsheet.com/discussion/comment/346097#Comment_346097

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

https://community.smartsheet.com/discussion/comment/346112#Comment_346112

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.