Building Estimation Spreadsheet

**gotoplanf** · 07-06-2012, 02:32 PM

Hello,

I consider myself a basic user of excel. I am well versed in many coding languages.(html/css/java etc) I can setup data tables, simple formulas (some complex =if), make calculations that sort of thing.

I have recently been tasked with setting up an estimation spreadsheet for our company. Basically we have our suppliers price list, with item description, name, cost, delivery etc.; from 3 different suppliers.

I can get those tables setup and can manually on a separate table assemble my quote/estimate by clicking =and click the direct cell.

What I would like to do is be able to enter a product code from one of those lists and have it show up in the estimate sheet. eg. Code 1001 12 yds topsoil $197.

Is this possible with Excel Mac 2011. Is it beyond my learning capability.

I thank you for your time in responding.

**Xer2** · 07-06-2012, 03:23 PM

Could you supply a sample workbook?

**gotoplanf** · 07-06-2012, 03:51 PM

Attached is a sample.

Basically rocks sheet would have a list of the items with there respective info.

Quote sheet would have a list where you could punch in an item code with a quantity amount and the rest would self calculate.

thanks

Couldn't attach spreadsheet so just took screen caps

products.jpg quote.jpg

**FDibbins** · 07-06-2012, 04:10 PM

this is very basic, but it gives you what you asked for. i used data validation in column H, so if you click on the pull-down arrow, you can select your item.

let me know how this works for you

**gotoplanf** · 07-06-2012, 04:23 PM

This works.
To put the quote/estimate on a separate sheet I would just have to use validation from the sheet i need. Can you breakdown how the vlookup function works, so I understand what is going on there.

Please Login or Register  to view this content.

Thanks

Originally Posted by FDibbins

this is very basic, but it gives you what you asked for. i used data validation in column H, so if you click on the pull-down arrow, you can select your item.

let me know how this works for you

**oeldere** · 07-06-2012, 04:51 PM

Look up:

cell H2

In the range A2:D7

if found the valua in cel H2 choose the value in collumn 2

False (True is the other option)

This is the option to determine if you need an exact match.

**gotoplanf** · 07-06-2012, 05:09 PM

Awesome thank you. You have been so helpful. I have one further monkey wrench to throw in. One supplier has a price per yard, 3/4 yard, 1/2yd and 1/4yd. They are not simple division of each. It is a value that grows in discount as you buy more.

Is there anyway to look up across the values of the table against the value of an arbitrary quantity I would enter on the quote portion. i.e. if i enter .75 yds it would look up the item, look for the value associated at the 0.75 column and add that to the quote. Then if I entered 1 or greater it would take the 1yd column and multiply by that total. Ie I enter 3yds it would look up the item. Check the 1 column and times by 3

I have added a new example file.

Thanks again for all your help.

sample changed.xlsx

Originally Posted by oeldere

Look up:

cell H2

In the range A2:D7

if found the valua in cel H2 choose the value in collumn 2

False (True is the other option)

This is the option to determine if you need an exact match.

**FDibbins** · 07-06-2012, 06:02 PM

your codes on sheet1 already include the 1, .75, .5 and .25 sizes, so why would you need to apply them seperately? alternatively, remove them from your list, shorten the list and then have the ability to enter a "volume"?

**gotoplanf** · 07-06-2012, 11:09 PM

Ok. I decided to drop the codes and just use a validation list of the items. I have a crazy 200 character long series of IF statements that seems to work with the 1,0.75, 0.5, and 0.25 sizes. In there it is declared if greater than 1 to times by the quantity and cost from the 1 column. It's probably the long way around. But with the help of that VLOOKUP function, I was able to conquer it. Thanks for all the help. I now understand the validation tool which is great because i want to have 5 or so sheets of data, and then one clean sheet styled in a printable quote format with info pulled from everywhere. If someone wants to look at the crazy formula that worked I will post the sample sheet.

sample changedj.xlsx

**oeldere** · 07-07-2012, 03:04 AM

If

1) you can make your data on sheet 1 the way i made them

And

2) the cost are in the table on sheet 2

You can use index/match (see my example => the yellow cells).

I made it with text to collumn

CNTL H
find yrd
replace (leave empy)

do you have questions on this item, just ask.