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

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

On Sep 20, 4:41*pm, rp <phi...@free.fr> wrote:
> 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

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

"rp" <(E-Mail Removed)> wrote in message
news:adb615da-0fa4-4069-9d0f-(E-Mail Removed)...
> 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
>

On Sep 20, 11:37*am, "Jim Rech" <n...@abc.com> wrote:
> 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
>
> "rp" <phi...@free.fr> wrote in message
>
> news:adb615da-0fa4-4069-9d0f-(E-Mail Removed)...
>
>
>
> > 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- Hide quoted text -
>
> - Show quoted text -

If all else fails, send me the file.