Combine Multiple Functions into the Same Formula in Excel

0

I watched your tutorial that is used to combine functions, yet i can't seem to get it to work in my document. I'm trying to use the hyperlink Function to go to a specific cell in my document. here's a piece of my code that i used:

=HYPERLINK("#Blad1!=CELL("contents",K33)","Totaal")

The Cell K33 Contains the cell number which can change from E117 to E255 Depending on the current date and time. Currently the content of cell K33 is E117, but tomorrow for instance it will be E118. If you could help me out with what i'm doing wrong in the functions, please let me know.

Greetings.

Answer
Discuss

Answers

0

Stripped of its complications, the hyperlink function you want would look like this.

=HYPERLINK("#Blad1!E117","Totaal")

Observe that the function takes two string arguments, sparated by a comma. That means that you problem is with a string. Anything you feed to the function that isn't a string won't work. And if it's a string that isn't a valid address another kind of error will result. That's the case with your formula. 

Your string looks like this:-

"#Blad1!=CELL("contents",K33)"

If it could be dissected it would translate into "#Blad1!=E117" which isn't a valid address. Remove the equal sign and it might work. But it won't. That's because in
"#Blad1!CELL("contents",K33)" the function CELL("contents",K33) is passed as a string instead of being evaluated. What you want is to concatenate the sheet name with the evaluation of the CELL function. That would look as shown below.

"#Blad1!"&CELL("contents",K33)

If you embed this string as the first argument in your HYPERLINK function it will do what you intend. The function below will also work. It uses the INDIRECT function instead of CELL which is less commonly used and a little more elaborate.

=HYPERLINK("#Blad!"&INDIRECT("K33"),"Total")
Discuss

Discussion

I'm sorry if I'm late with answering. But to be honest i don't know what half of it means. and just entering
=HYPERLINK("#Blad!"&INDIRECT("K33"),"Total")

doesn't work somehow. I don't if it's a problem, but i'm not using the latest version of excel. I'm using excel 2007.
I'm still a beginner when it comes to excel functions so if you could please explain it more simple next time. That would be great. Also my english is pretty good, but not as good as might be expecting, because i'm from the netherlands.
But if you need more info I can upload a part of the file which contains the code but not the values i'm using because they are private. I've already got the document ready to upload to anywhere you want. But do keep in mind that my version is 2007. Anyway, thanks for the quick respond, and sorry for my late one. Greeetings
F1reP0wer Jun 24, '20 at 2:21 pm
Here's a link to the document:
https://mir.cr/0FIYXAH6
F1reP0wer Jun 24, '20 at 2:33 pm
"It doesn't work" isn't a very useful explanation of your problem. If you upload you file here I shall take a look. You can go back to your question and edit it. In Edit mode you can then attach an Excel workbook.
Variatus (rep: 4098) Jun 24, '20 at 8:22 pm
Add to Discussion
0

Hi F1reP0wer,

I too use Excel 2007. Your formula is almost perfect except for a couple of syntax errors. Even one typo and the formula will barf.

Your original formula looks like this:

=HYPERLINK("#Blad1!=CELL("contents",K33)","Totaal")

Change your HYPERLINK formula to the following (it works for me)

=HYPERLINK("#Blad1!" & CELL("contents",K33),"Totaal")

Note: unless intentional, it appears that there is a spelling mistake in the 'friendly name' - did you mean it to be Total?

Hope this helps.

Cheers   ;-}

Discuss

Discussion

It's the way to spell the Dutch word for total.
Variatus (rep: 4098) Sep 13, '20 at 8:16 pm
I always found it fun how the dutch language seems keen to double-up their vowels when they can)
don (rep: 1900) Sep 14, '20 at 4:41 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login