Named range not reporting in another worksheet 1

[williekay]

I have two Excel spreadsheets I've used for years. A worksheet in one has three named ranges that represent the ITEM you wish to reference, the FIELDS, and the TABLE the information is in. In another spreadsheet I have formulas that use these names to return information ---

=IF(ISERROR(INDEX(TABLE, MATCH($C10,ITEM,), MATCH(E$9,FIELDS,))),"",INDEX(TABLE, MATCH($C10,ITEM,), MATCH(E$9,FIELDS,)))

I've added another worksheet to the spreadsheet with the named ranges. I've added three new named ranges and have put a simular formula in the other spreadsheet. These named ranges are not recognized in this spreadsheet or any other one I put them in, but the before referenced formula still works and the only link to the spreadsheet and the test spreadsheets I've tried are to this book ???? I've also changed the new named ranges a dozen times and still nothing. I've tested new spreadsheets using a simular setup - one with named ranges and one with a formula - they work - it's just referencing these new named ranges that will not work.

Willie

 

[SkipVought]

Hi,

And the new named ranges are named what?

What happens when you use these named ranges in thse same workbook?

Is the Workbook (please use workbook rather than spreadsheet) saved in the same folder as the others?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

If your New Names are names that already exist in that workbook, you must ALSO include the Sheet Name in the reference, like
[tt]
MATCH($C10,Sheet1!ITEM,)
[/tt]


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[williekay]

Thanks Skip - I don't know why but placing the formula in the same book with the named ranges and then pasting it into the other book made it work.

Willie