# Create a Macro or Formula to automatically extract and calculate costs

#### PostIt

##### New Member
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.

Running:
Microsoft Office Home and Student 2010

### Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
Have you tried a VLOOKUP.

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.

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

igold,

Hi Postit,

I PM'ed you.

igold

Replies
2
Views
1K
Replies
0
Views
1K
Replies
1
Views
237
Replies
2
Views
720
Replies
4
Views
473

1,196,340
Messages
6,014,710
Members
441,838
Latest member
ykg1991

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back