data validation list – not working! (excel 2000)

Topic

New Reply

I have multiple workbooks that can be opened by hyperlinks. All workbooks are using data validation lists. The lists work when I open the files directly from an open window, however, they don’t seem to work when the files are opened by a hyperlink. Now the validation lists are depended on a list from a particular workbook that is opened at all times. Why is excel doing this? And how can I get the validation lists to work even when a workbook is opened by a hyperlink?

Thanks

Reply | Quote

Replies

If your linked files are dependent on an an open file, when the hyperlinks OPEN the files, are they using the SAME version of excel or opening another occurence of excel? If they are opened in a NEW version of excel, to the 2nd version, the other file is NOT open.

Steve

Reply | Quote

I see, but I created everything in the same version of excel. So I believe that the same version is applied to all workbooks.

Reply | Quote

Not version as in XL97, but OCCURENCE of excel. You can open excel 1 time and have multiple workbooks open in separate windows.

You can also open up multiple occurences of excel and have EACH workbook in a separate XL occurence.

After you click on the hyperlink, and open the NEW file, can you switch back and froth among the new and one with hyperlinks from the WINDOW or must you use windows taskbar?

Steve

Reply | Quote

Yes I can switch between windows by just clicking on them ( clicking on the windows at bottom of the screen that are opened) without having to go to the windows taskbar. However, I did notice that after going back to the previous window, then clicking back on the window with the hyperlink (which was used to open the file) that the computer seems to pause for a few seconds before opening the file back up. When the file is first opened using the window (from path in my computor icon) it does not take as long. If this sounds like it may be the case (with the workbooks in different occurences), can I get all of them in the same occurence? If so, how?

Reply | Quote

I still don’t think we are on the same page. My “hypothesis” is that the reason your validation does NOT work is that it is linked (indirectly) to a cell in a separate workbook. For the connection to be made with INDIRECT this linked workbook MUST be open.

Since you claim it IS open, but does NOT work in the hyperlink, but works when you open it normally, I hypothesize that you are opening the hyperlink in a SEPARATE session of excel (you have 1 version running and the hyperlink OPENS another session).

I am NOT yet sure from your responses if you have multiple sessions or not. I have NOT been able to change my XL97 to get it to open a different session, so this theory might be wrong, or XL2000 allows some differences with hyperlinks.

When you hyperlink, does it open a NEW copy of excel into memory.?
Steve

Reply | Quote

No, it doesn’t. It goes to the same workbook opened before (if thats what you mean by NEW copy of excel into memory). If it clears anything up – I should let you know that after hyperlinking into a workbook that has already been opened by windows (in my computer icon), the validation list works find. Only when the file isn’t previously opened (by windows) – then opened by the hyperlink itself is when the validation list does not work. But I’m not really sure what you meant by the question – does it open a NEW copy of excel into memory?

LaMont

Reply | Quote

You open the file with the hyperlinks. You click on the hyperlink to open the file of interest.

Does it open in the same session of excel or does excel load again (so you have 2 sessions of excel running) or is it a new workbook in the currenty session?

Steve

Reply | Quote

hey Steve,

Yes, when I open a workbook by hyperlink it is opened in the current session of excel – excel doesn’t load again.

LaMont

Reply | Quote

Could you attach a simple copy of a workbook with the data validation you are using in a cell?

I would like to understand how it “doesn’t work” when called via a hyperlink. If you have to also attach a copy of the calling program (that is also open) do so.

Please make sure that any example ones you attach, “do NOT work” in the same way that your “real ones” do NOT work.

Steve

Reply | Quote

I’m sending you three workbooks that should be a good example. Workbook ex3.xls is the file the contains the data which the validation list is dependent on. This file should be opened first and remain open (I sometimes hid the file). Workbook ex2.xls contains the hyperlink that opens the workbook ex1.xls. Workbook ex1.xls contains the validation lists.

Reply | Quote

workbook ex2

Reply | Quote

workbook ex3

Reply | Quote

I saw 2 potential problems:

1) I think this is probably due to example file not being complete. The data validations for the description in EX1 (col read the value from Column A. They will ONLY work for “LineType” since the other 10 elements do NOT have defined names.

2) I will assume you are reading opening the files from network servers since that is when I saw a problem. EX1 Column A data validation grabs from the named range “ELEMENT”

You defined element as:
='[ex3.xls]Ratings’!$A$6:$A$16

When opened (NOT via the hyperlink) this is fine, but when OPENED via a hyperlink on a network, the named range will automatically include the full path to the server:

ELEMENT is:
=’serverfolder1folder2[ex3.xls]Ratings’!$A$6:$A$16

You can verify by (after opening via the link) and then go to Insert -name – define and check the refers to for ELEMENT.
ELEMENT will NOT find the OPEN file named:
‘[ex3.xls]Ratings’!$A$6:$A$16
since it is LOOKING for one on the server:
=’serverfolder1folder2[ex3.xls]Ratings’!$A$6:$A$16
and the datavalidation does NOT work with an UNOPENED copy (ONLY with OPENED files).

To FIX this, change the refers to on ELEMENT to:
=INDIRECT(“[ex3.xls]Ratings!$A$6:$A$16”)

and it NOT append the server and path and only look for an OPEN file.

If this does NOT solve it, you will have to be more specific about the problem.

Steve

Reply | Quote

Yup! That seemed to be the problem and its works find now. All of the files are on a network server. I never had a clue!

Thanks a great deal
LaMont

Reply | Quote