Excel Fomula

By jimdaddy7up ·
I have created a workbook with 5 worksheets each containing a list of cabinets(column 1) and a list of sizes(row 1) with prices in the respective cells. Each worksheet is a different material category therefore different prices. What I want to create on another worksheet is an order form with drop down boxes referencing (1)the material series or worksheet#, (2)the cabinet or row#, (3)the width or column# and (4)various other data columns to return the prices of the cabinets chosen. So far I have this formula;
It all works except for the first part CabSeries which should select the appropriate worksheet, CabSeries1, CabSeries2, etc. If I enter one of these manually it works but if I try to choose it with a drop down box I get an error. Who wants to give it a try? I can e-mail the file if needed.

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

Collapse -

I couldn't make it work

by LocoLobo In reply to Excel Fomula

The suggested formula in the help was


In their example they showed something like


This example returns the second row, second column of the second area (A6:E9). Which works if the areas are in the same worksheet. But trying the formula


returned #VALUE.

I also tried creating a named range using multiple worksheets and using the name in the reference. Then the formula returned #REF.

Here's a Klugy workaround. Create another worksheet. In this worksheet build 5 areas that get the numbers from each of the other worksheets. Then your formula should work.

Hopefully somebody else knows how to properly reference multiple worksheets.

Related Discussions

Related Forums