the menu of function data VALIDITY shows exactly the list referred to, range e.g. a1:a10.
If the list consists of URLs the menu shows http:____________, hard to find which URL applies.
Therefore as in URL in Writer, 2 or 3 fields are required:
1) the actual URL
2) A name for it
GOAL: The validity menu should show the name for, not the actual URL. It uses cell range or list, as selected while defining it.
The validity menu cell is b1,
The actual hyperlink click cell is b2. containing
=hyperlink(validity:cell)
or
=hyperlink(b:1)
how use names for this application?
I shall enjoy a HYPER-ANSWER
[Solved] Data Validity list with names for choice
- morphingstar
- Posts: 100
- Joined: Mon Mar 28, 2011 5:52 am
- Location: Mx
[Solved] Data Validity list with names for choice
Last edited by Hagar Delest on Thu Sep 19, 2019 9:50 pm, edited 1 time in total.
Reason: tagged solved
Reason: tagged solved
OO 4.1.6 on Win10. Member since StarOffice 7. Alternative name "morphingstar2" when other fails. Use Writer, Calc (as DB + Calc), Draw. Impress mainly for Eyedropper and animating GIFs. Updated 20190716.
Re: Data Validity list with names for choice
Have you tried using the second parameter of the hyperlink function to point reference the actual selection?
How is the validity menu supposed to know that you want the name, rather than the URL displayed?morphingstar wrote:GOAL: The validity menu should show the name for, not the actual URL. It uses cell range or list, as selected while defining it.
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
- morphingstar
- Posts: 100
- Joined: Mon Mar 28, 2011 5:52 am
- Location: Mx
Re: Data Validity list with names for choice
1) by designHow is the validity menu supposed to know that you want the name, rather than the URL displayed?
2) by workaround, which refers to the subject of this thread.
3) by something I did not find out / have not thought of
Happy?
OO 4.1.6 on Win10. Member since StarOffice 7. Alternative name "morphingstar2" when other fails. Use Writer, Calc (as DB + Calc), Draw. Impress mainly for Eyedropper and animating GIFs. Updated 20190716.
Re: Data Validity list with names for choice
morphingstar wrote:1) by design
2) by workaround, which refers to the subject of this thread.
RusselB wrote:Have you tried using the second parameter of the hyperlink function to point reference the actual selection?
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
- morphingstar
- Posts: 100
- Joined: Mon Mar 28, 2011 5:52 am
- Location: Mx
Re: Data Validity list with names for choice
2nd parameter
1 choice shown in Validity:
LX4 Upgrade keine Verbindung 20190813.jpg NAME="NameText"
Path, file, desired alternative Name
C:\Users\ALL\Pictures\_printscreen\LX4 Upgrade keine Verbindung 20190813.jpg NAME="NameText"
from Calc HELP
"http://www.example.com/" NAME="Nametext"
Windows can not find.
the HELP example shows a html version, this URL does not seem to operate in Calc.
Validity does not split path+file ¦ Name. It can only handle 1 cell at one time.
Setting Validity cell to right adjust helps nothing. Use of slide bar required. Clumsy.
1 choice shown in Validity:
LX4 Upgrade keine Verbindung 20190813.jpg NAME="NameText"
Path, file, desired alternative Name
C:\Users\ALL\Pictures\_printscreen\LX4 Upgrade keine Verbindung 20190813.jpg NAME="NameText"
from Calc HELP
"http://www.example.com/" NAME="Nametext"
Windows can not find.
the HELP example shows a html version, this URL does not seem to operate in Calc.
Validity does not split path+file ¦ Name. It can only handle 1 cell at one time.
Setting Validity cell to right adjust helps nothing. Use of slide bar required. Clumsy.
OO 4.1.6 on Win10. Member since StarOffice 7. Alternative name "morphingstar2" when other fails. Use Writer, Calc (as DB + Calc), Draw. Impress mainly for Eyedropper and animating GIFs. Updated 20190716.
Re: Data Validity list with names for choice
=HYPERLINK(INDEX($A$1:$A$4;MATCH(D1;$B$1:$B$4;0));D1)
where A1:A4 is the column of URLs, B1:B4 is the column of labels and D1 is the validated list linked to the column of labels. The formula returns a clickable hyperlink in some other cell.
=HYPERLINK(INDEX(URLs ; MATCH(validated_cell ; labels ; 0)) ; validated_cell)
where A1:A4 is the column of URLs, B1:B4 is the column of labels and D1 is the validated list linked to the column of labels. The formula returns a clickable hyperlink in some other cell.
=HYPERLINK(INDEX(URLs ; MATCH(validated_cell ; labels ; 0)) ; validated_cell)
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
- morphingstar
- Posts: 100
- Joined: Mon Mar 28, 2011 5:52 am
- Location: Mx
Re: Data Validity list with names for choice
Solved using the method in the attached file
- Attachments
-
- validity substitute with Lookup+IF .ods
- (29.28 KiB) Downloaded 85 times
OO 4.1.6 on Win10. Member since StarOffice 7. Alternative name "morphingstar2" when other fails. Use Writer, Calc (as DB + Calc), Draw. Impress mainly for Eyedropper and animating GIFs. Updated 20190716.