Software

Ask something
Question Locked

Excel Fomula

0 helpful
2 replies

by jimdaddy7up

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

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.

2 replies

by locolobo

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.

This discussion is closed to new replies.