Create a Macro or Formula to automatically extract and calculate costs

[PostIt]

Hello,

I don't think it's possible but I've been trying to figure out a way to extract data from sheet 2 to my main sheet.

We manufacture jewelry and I'm in charge of recalculating all our product prices.
Each item has a different shape, size and quantity of stones.
The stone prices are on sheet 2.
The stone shapes, sizes, and quantities are on the main sheet next to each item #.
I was wondering if there was a way to pull the prices from sheet 1 based on the information on my main sheet to automatically calculate the costs.

Please advise.

Running:
Windows 7 Home Premium
Microsoft Office Home and Student 2010

 

[igold]

Have you tried a VLOOKUP.

 

[PostIt]

That's what I used for the first row only in my main sheet and it worked fine.
In the event there are more than 1 stone shapes and sizes, how would that work?
What if there were 5 different stones/sizes?
Would everything still have to be input manually?

For example, I tried it for one item and it looks like this...
=(VLOOKUP("SQ 2.5, Sheet1!A2:C472, 3, FALSE))*71
SQ is the stone shape.
2.5 is the stone size.
VLOOKUP has to go through Sheet1 and those cells.
3 is the column the prices are in.
The number has to be exact.
71 is the quantity of stones.

Thank you for the VLOOKUP suggestion.

 

[igold]

Hi Postit,

Could you possibly show a representation of how each sheet is laid out. It can be purely fictitious data but I would like to see how the Main sheet with item numbers look and sheet2 with the pricing matirx. This really should not be too hard to set up for you. Also it can be extremely abbreviated, like two or three different items and two or three different stones and prices.

igold

 

[PostIt]

igold,

Please see below.



If you need more information, please let me know.

 

[igold]

Hi Postit,

I PM'ed you.

igold