[Solved] Data Validity list with names for choice

Discuss the spreadsheet application

[Solved] Data Validity list with names for choice

Postby morphingstar » Wed May 01, 2019 9:35 pm

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
Last edited by Hagar Delest on Thu Sep 19, 2019 9:50 pm, edited 1 time in total.
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.
User avatar
morphingstar
 
Posts: 69
Joined: Mon Mar 28, 2011 5:52 am
Location: Mx

Re: Data Validity list with names for choice

Postby RusselB » Wed May 01, 2019 10:33 pm

Have you tried using the second parameter of the hyperlink function to point reference the actual selection?
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.

How is the validity menu supposed to know that you want the name, rather than the URL displayed?
OpenOffice 4.1.7 and LibreOffice 6.3.3.2 on Windows 7 Pro & Ultimate
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.
User avatar
RusselB
Moderator
 
Posts: 5677
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Data Validity list with names for choice

Postby morphingstar » Thu May 02, 2019 3:07 pm

How is the validity menu supposed to know that you want the name, rather than the URL displayed?

1) by design
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.
User avatar
morphingstar
 
Posts: 69
Joined: Mon Mar 28, 2011 5:52 am
Location: Mx

Re: Data Validity list with names for choice

Postby RusselB » Thu May 02, 2019 3:19 pm

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 and LibreOffice 6.3.3.2 on Windows 7 Pro & Ultimate
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.
User avatar
RusselB
Moderator
 
Posts: 5677
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Data Validity list with names for choice

Postby morphingstar » Wed Aug 14, 2019 7:34 am

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.
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.
User avatar
morphingstar
 
Posts: 69
Joined: Mon Mar 28, 2011 5:52 am
Location: Mx

Re: Data Validity list with names for choice

Postby Villeroy » Wed Aug 14, 2019 3:57 pm

=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)
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27376
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Data Validity list with names for choice

Postby morphingstar » Wed Sep 18, 2019 11:35 pm

Solved using the method in the attached file
Attachments
validity substitute with Lookup+IF .ods
(29.28 KiB) Downloaded 9 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.
User avatar
morphingstar
 
Posts: 69
Joined: Mon Mar 28, 2011 5:52 am
Location: Mx


Return to Calc

Who is online

Users browsing this forum: No registered users and 29 guests