Question

Locked

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;
=INDEX(CabSeries,MATCH(C5,CabConfig,0),
MATCH(D5,CabWidth,0))
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.

1 total post (Page 1 of 1)  
| 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

=INDEX(reference,row_no,column_no,area_no)

In their example they showed something like

=INDEX((A1:E4,A6:E9),2,2,2)

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

=INDEX((SHEET1!A1:E9,SHEET2!A1:E9),2,2,2)

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.

Back to Software Forum
1 total post (Page 1 of 1)  

Related Discussions

Related Forums