I have created an excel workbook. On one page I have an item list numbered from 4100 to 6000. Each item has a description and a corresponding rate.


Then I created an estimate page. I want to enter an item number on the estimate page and get the corresponding description and rate on the estimate.

I need a formula that will allow me to do this.

And what is an estimate?
And what are rules to calculate it?

An estimate is the same as a bid proposal. I have a item list of Materials and Equipment that have fixed cost rates. I have given each material and piece of equipment and item number on a worksheet page.
On the estimate page I want to pull the item number from the item list Such as item 4100 in column A21, which is a piece of equipment described as a 228 Backhoe in column B21, with a rate of $228 in Column C.
I want to be able to just type in 4100 in one cell, and have the Description appear in another cell and the rate in another cell.

Something like this?

Attached Files

Estimate.xlsx (11.8 KB, 8 views)

@klein
Hi and welcome
please note that current courtesy is not considered offensive on public free forums.
A " Hello" or "thank you" is always appreciated.
Cheers

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Hello and thanks for the information. The formula is exactly what I need it to do. I'm guessing I need to do some other steps to my worksheet to make this work, like define names maybe? Not sure how to set that all up. Attaching my workbook for you to see

Attached Files

J & S Estimate Worksheet.xlsx (22.0 KB, 10 views)

I attached the wrong Workbook. Here is the correct one. Thank You!

Hello again, I got the formula to work. I had to redefine the name so it would work in other cells, but it turned out great. Thank you so much for the help. I've never used the IFERROR formula, this is a new experience for me. Thanks again, you have solved my problem.

I had a look at your attached table. Some remarks.

Keep all summaries at top of page - then you don't have to bother about rows you may add at bottom of table;
Don't use full-column references when you can avoid this;
Don't use fixed Named Ranges whenever the range dimensions may change - use dynamic Named Ranges;
When you aren't planning to use the workbook as shared, use defined Tables instead Named Ranges. Defined Tables have specific syntax to refer to its various elements (header, datarange, datarange column, cell in same row, etc.), adjust formulas automatically whenever you change Table headers, used formulas usually aren't sensitive to order of columns in table, adding some data into any cell immediately below last row of Table expands the Table automatically, and unless you have several different settings in same column, all formulas, formats, and validations are automatically expanded whenever new rows are added.

As example, in attached file I designed a sheet Estimate similar to your one.

Both Tables on sheets Items and Estimate have a couple of helper columns added (colored differently, they are meant to be hidden). And the Table on sheet Items has also column SelectedQty, where you can enter the quantities for items, you want to include into selection.

On sheet Estimate, the Table is automatically filled with selected items, their quantities, and monetary amounts.

For sheet Estimate, the Print Area is automatically adjusting to number of rows in Estimate Table.

The Total on sheet Estimate shows total of filtered amounts, i.e. when you set some autofilter to Estimate Table, only visible Amounts are totallied.

I didn't use it, but as example I defined a dynamic Named Range nItemTbl. It is possible to redesign all formulas to refer to this range instead of table (but you lose some functionality of-course). I myself prefer Tables. There are cases where I have to use Names too (validation lists and conditional formats don't recognize Tables), but then I mostly define a Table, and then a Named range as some part of this Table.

Attached Files

Estimate.xlsx (24.3 KB, 9 views)

Thanks for this Arvi and Klein. I will show it to our engineer whose task is to prepare bills of quantities, although I am aware that the engineering office has its own system, I am sure the template would be of great help to them.

Arvi did a great job with this workbook. Excel is a powerful tool. I just wish I knew more. I certainly hope it can help someone else. Good luck!
Thanks,
Klein