Code clean-up

Ferdib

New Member
Joined
Jan 7, 2020
Messages
15
Office Version
  1. 365
Platform
  1. Windows
Hi MrExcel,

I was wondering if someone with a little more experience could look at the following code and maybe tell me a better way of doing it (or clean it).

VBA Code:
=IF($F$14=15;HYPERLINK("#'SVN RMA'!L41";"Click here for an email template.");IF($F$14=10;HYPERLINK("#'SVN RMA'!L41";"Click here for an email template.");IF($F$14=24;HYPERLINK("#'SVN RMA'!L41";"Unknown RMA error. Please create manual RMA.");IF($F$14=17;HYPERLINK("#'SVN RMA'!L41";"Master not filled in. Please create manual RMA.");IF($F$14=8;HYPERLINK("#RCHT!W2";"Click here to go to the Help Tool!");IF($F$14=9;HYPERLINK("#RCHT!W2";"Click here to go to the Help Tool!")))))))

Explanation:
- It basically says: IF F14 = [number] then display [text/link]
- This code is located in L41.
- F14 contains the results of 25 radio buttons (1-25).
- The code looks at F14 which number is displayed (depending on which radio button was selected).
- The results in L41 can contain either specific text or a specific link.
- Links include places in the workbook (on different sheets) or external files (like an Outlook draft email)
The reason I say a clean-up is "needed", is because when plain text is displayed, the cell still sees this as a link.
For now I made a workaround for the text by letting it think its a link and link it to the same cell - If I don't make it a link, Excel takes the next link in the formula and connects it to the plain text.
I'm sure there is a better way of doing this....I tried CHOOSE, but that only copies plain text and no links.

Many thanks in advance!

Kind regards
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
For clarity, what you have is a formula not code, they are completely different things.
The reason I say a clean-up is "needed", is because when plain text is displayed, the cell still sees this as a link.
This is how excel behaves when you use the hyperlink function, there is no way of having text without a link when you have a link function in the cell, at best you could replace your plain text with a link back to the cell that the formula is in, which I think it what you're saying that you have done.
You could possibly shorten the formula a bit, but there is nothing wrong with what you have.
 
Upvote 0
You're welcome :)

Following up, I notice that your formula only has 6 options so far, do you need to add a link to the formula for each of the 25 radio buttons?

I've added a slightly shorter method below for you to take a look at, which I think should work but I haven't tested it.

=IF(OR($F$14={10;15;17;24});HYPERLINK("#'SVN RMA'!L41";LOOKUP($F$14;10;17;24};{"Click here for an email template.";"Master not filled in. Please create manual RMA.";"Unknown RMA error. Please create manual RMA."));IF(OR($F$14={8;9});HYPERLINK("#RCHT!W2";"Click here to go to the Help Tool!")))

By far the easiest method to manage would be a lookup table, but I don't know if the hyperlinks will be passed correctly. In theory they should, I do remember a previous thread where hyperlinks were not behaving as expected, but I don't remember the reason for it.
 
Upvote 0

Forum statistics

Threads
1,213,517
Messages
6,114,089
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top