Get Conditional Formatting to stick with Followed Hyperlinks
I am attempting to build a form within Excel that requires hyperlinks in a dropdown format. I know this can only be done with the HYPERLINK formula. But I only want it to appear visible when something is selected in the drop down so I have a conditional format that makes the text WHITE when the field is blank. This works until the link is clicked. Once it's clicked, its like the conditional formatting ceases to work. How can I get Conditional Formatting to work after the link is clicked.
Last edited by jespo124; 01-02-2020 at 12:13 PM. Reason: Adding attachment
Welcome to the forum.
I'm struggling to visualise this. Please attach a sample workbook - instructions at the top of the page.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Attachment has been added. The end user experience should be
- Pick a catagory in cell A5
- Pick a drop down item in grey boxes A12, C12 or E12
- A VLookup function in cell A13, C13 or E13 pulls in the link data from Sheet 2
- The end user sees text in A14, C14, or E14 with a link (HYPERLINK function) directing them to click to get the content
The last step is the one with the conditional formatting that is the grey boxes are empty that the text in A14, C14 and E14 should be white (so the user can't see it).
In my attachment you can see tha to be true for E14 because the link has never before been clicked but A14 and C14 are still showing blue links because they have been clicked before.
Does this help?
Cells A14, C14, and E14 are all blank.In my attachment you can see tha to be true for E14 because the link has never before been clicked but A14 and C14 are still showing blue links because they have been clicked before.
Furthermore the data validation in cells A12, C12 and E12 does not work so it is hard to replicate the issue.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
So cells A14, C14, and E14 are intended to be blank when the document loads for the first time. And the data validation in cells A12, C12 and E12 is reliant on A5 being picked.
If you pick Sample 1 in cell A5; A12 can be dropped down to Facebook which loads a link into A13 and then A14 brings up the text 'Go to Einstein Document listed'
After you click on A14 and it takes you to the website - click Reset Tool and you'll notice that A14 is no longer blank (it shows a link of #N/A)
It's this action (showing a link of #N/A) that I am attempting to fix because the conditional formating I have built in doesn't seem to be working once the link is followed.
I used the following formula as a conditional formatting rule applied to cell A13: =A12=""
I set the font to white as you mentioned in post #1 and it hid the #N/A once reset tool button was selected.
I might mention that you could also accomplish displaying a blank in cell A13, after resetting, by modifying the formula to read: =IFERROR(VLOOKUP(A12,Sheet2!B:C, 2, 0),"")
Let us know if you have any questions.
Thanks for looking at it JeteMC. I'm not really worried about how A13 looks as it is generally a hidden cell - my concern is getting A14 to go back to being white once the link is selected. I tried your suggestion using conditional formatting on A14 with the formula of '=A12=""' but it still shows the link when the form is reset. Am I missing something?
I feel as if I missed what you wanted, hopefully I understand now.
Try modifying the formula in A14 to read: =IFERROR(HYPERLINK(A13, "Go to Einstein Document listed"),"")
Note that this works after cell A17 is selected.
Let us know if you have any questions.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks