I am having a problem using INDIRECT that I can not seem to solve.
I have two separate workbooks.
Workbook #1 contains the data columns, so I will call it DataWkBk.xls (I used excel 2003 settings).
Workbook #2 contains the working formulas in which I wish to have three drop down menus, I will call it FormulaWkBk.xls.
There are three sequential dropdowns on the formulas workbook.
The user first selects from a list of categories that are a dropdown of data entries from Workbook #2.
Whatever the selected option in the first drop down menu is, determines which list will appear on the second drop down menu.
Standard stuff for the INDIRECT function.
But the INDIRECT function only seems to work if I define the EXACT range of cells on the second workbook.
The following example works, as I define the exact cell range on the second workbook (DataWkBk.xls):
Workbook #1 Data Validation entries for the first dropdown menu is:
DV_Categories.jpg
This works fine and the user selects from the list of possible Categories of products that then appear in this cell, call it cell A1
Cell A2 then is intended to provide a dropdown menu based on whatever text string appears in cell A1.
So its Data Validation entries are this:
DV_INDIRECT.jpg
This will work fine as long as I define the EXACT range of cells that are to be looked at on the second workbook.
This works:
Specific_Cell_Range.jpg
Unfortunately, as with all data files, the rows and columns increase and change as more data is added.
So I set up a Named Range on workbook #2, one Named Range for each possible category of products.
I then cross-referenced these named ranges (successfully) in workbook #1.
But when I use a Named range with the INDIRECT function it does not work, I get a "results in zero" message:
Named_Range.jpg
Yet, when I manually enter the cross-referenced Name, it works just fine.
I replace the =INDiRECT(A1) with =CLIMATE (or any other Named Range) and it works just fine.
I defined the names as follows using Name Manger (Excel 2010) Refers to: DataWkBk.xls!Climate
Direct_Usage_of_Named_Range.jpg
I believe it has to do with INDIRECT not copying strings of text, and the Named Ranges need text.
Is there some other function beside INDIRECT that I can use to copy the text selected in cell A1 into the Data Validation formula of cell A2?
..........Carl
Bookmarks