Hyperlink formula to sheet not Working

[howard]

I have a hyperlink value, but when I click on the hyperlink, I get an excel security warning message and when I click on Yes, It says it cannot find specified file


However, If I right click and manually create a hyperlink to the sheet, it works

 =IF(A14="% of Sales","",IF(OR(A14="Grand Total",B14=""),"",HYPERLINK(INDIRECT("A14"),OFFSET(A14,0,0))))

it would be appreciated if someone can kindly assist me

 

[Special-K99]

Should that be INDIRECT("A14") or just A14 ?

 

[howard]

thanks for the reply


I have a text names in A14 to A30 that refer to sheet names for eg BR1, Br2 etc


Please see my sample data below


Kindly amend my code

Book1
	A	B	C	D	E	F
14	BR1					BR1
15	BR2					BR2
16
17
18
19
20
21
22
23
24
25
26
27	Grand Total
28
29
30	% of Sales
31
32
33
34
35
36
Sheet1

Cell Formulas
Range	Formula
F14	=IF(A14="% of Sales","",IF(OR(A14="Grand Total",A14=""),"",HYPERLINK(INDIRECT("A14"),OFFSET(A14,0,0))))

 

[Special-K99]

Your logic is incorrect

IF(A14="% of Sales", then IF(A14="Grand Total or its blank...

well it wont be neither of those as youve just specified its "% of Sales"
e.g.
IF(A1=1,IF(OR(A1=2,A1=3....

If A1 =1 it can NEVER be 2 or 3 at the same time so in your IF(A14is "% of Sales"... the IF(OR... will ALWAYS fail.

Might be better to explain in words what youre trying to achieve.

 

[howard]

Thanks for your input

The formula in words is supposed to do the following

If the text in A14 contains either "% of Sales", or "Grand Total" then a blank is to be returned, otherwise return the hyperlink in A14 (the sheet name is in A14, A15 etc)

When copying the formula down into the next cells , I added the offset function to refer to the next cell for the hyperlink for eg A15 etc


Please advise if you need further information

 

[Special-K99]

That puts a different light on it.

One of these should work (untested)

=IF(OR(A14="% of Sales",A14="Grand Total"),"",HYPERLINK(A14))

or
=IF(OR(A14="% of Sales",A14="Grand Total"),"",HYPERLINK(INDIRECT("A14")))

 

[howard]

Thanks for your help. When clicking in the Hyperlink, I get a message "Cannot open the specified item"

Book1
	A	B
14	BR1	BR1
15	BR2	BR2
16
17
18
19
20
21
22
23
24
25
26
27	Grand Total
28
29
30	% of Sales
31
32
33
34
35
36
37
38
39
40
41
Sheet1

Cell Formulas
Range	Formula
B14	=IF(OR(A14="% of Sales",A14="Grand Total"),"",HYPERLINK(A14))

I have also shared the file "Hyperlink formula.xlsx" , which is a sample file on Box


https://app.box.com/s/7hehkd83wpcftlzj7480jft2rmp8ezuc


Kindly test & amend formula

 

[Special-K99]

Looks like you need # and the cell reference too
Try this

=IF(OR(A14="% of Sales",A14="Grand Total"),"",HYPERLINK("#"&A14&"!A1"))

 

[howard]

Thanks, It works perfectly


"