summary table from various sheets

[bruce]

I have several sheets detailing prices of widgets.
Each sheet is for a different material
Rows show width of widget, columns show height

Trying to make a simple quote sheet where I can enter the height and
width so that the sheet shows prices for that size in each material.

Tried LOOKUP, Pivot, address, indirect and 3d reference but can't get
any to do this.
Thought it would be easy!

Any advice welcome
(using Excel 2003 have Access 2000 if needed)

Thanks

 

[Earl Kiosterud]

Bruce,

It would be easy, but you've put the info in separate sheets. See
http://www.smokeylake.com/excel/excel_truths.htm. See "Data across multiple
sheets."

You need to make a single widget table (sheet), which would have whatever
you need to extract in single cells (width, height, etc. Then you can use
VLOOKUP against that table to get the data you need in your quote. You can
still have the sheets where you've used rows and columns of cells to sketch
the dimensions of the widgets, and a column in the widget table could link
to the appropriate sheet. Formulas in the widget table could probably
determine the height, etc., from the sketches, but that's a separate issue.

 

[bruce]

Earl
Many thanks
Great article, your expertise saved me a lot of time

Bruce