Can't located named range

[rp]

Hello,
one of my colleagues at work recently gave me an Excel spreadsheet,
which was developed by someone who left the company many years ago,
and asked me to modify the values contained in a cell data-validation
list. Unfortunately, I can't locate either the corresponding named
range in the current spreadsheet, or the list in the associated
spreadsheet.

Here's a more detailed description of my problem.

Cell D1 has a data-validation list whose four values are, say, "AAA",
"BBB", "CCC" and "DDD", and whose data-validation source is

=Named_range1

If I select Named_range1 in the named-ranged selection menu to the
right of the Excel Name Box, Excel does not display any particular
cell or cell range, as one would normally expect. Furthermore, if I
select Insert->Name->Define and click on Named_range1, I can see that
it refers to =Other_Spreadsheet!$K$1:$K$4

Unfortunately, I don't have a copy of Other_Spreadsheet.

My question is as follows: if neither the named range in the current
spreadsheet nor the external spreadsheet exist anymore, where does
Excel retrieve the list values ("AAA", "BBB", and "CCC") from?

If I create a new data-validated cell and make it use Named_range1 as
data source, Excel displays the source's values without any
difficulty. But where did it find them, given the fact that the source
does not exist anymore?

Many thanks.

pr

 

[Pete_UK]

What exactly does it have in the Refers to box?

If it does show =Other_Spreadsheet!$K$1:$K$4, then that is a sheet in
the same workbook, which has perhaps been hidden and you can't see it.
Just unhide the sheet.

Hope this helps.

Pete

 

[Jim Rech]

If the sheet Other_Spreadsheet is "very hidden" you have to unhide it with
code. This will also work if it's normally hidden. Run this in the
Immediate window in the VBE:

worksheets("Other_Spreadsheet").visible=xlSheetVisible

 

[Don Guillett Excel MVP]

If the sheet Other_Spreadsheet is "very hidden" you have to unhide it with
code.  This will also work if it's normally hidden.  Run this in the
Immediate window in the VBE:

worksheets("Other_Spreadsheet").visible=xlSheetVisible













- Show quoted text -

If all else fails, send me the file.