Error in cell function

[Guest]

I am trying to use the cell function to create a dynamic hyper link to
several sheets.
i can use: =HYPERLINK("#"&CELL("address",'Mall1 1-7'!A3),A2)
but i need to do a whole bunch and the sheet names change frequently so i
try:
=HYPERLINK("#"&CELL("address","'"&Intro!A3&" 1-7'"!A3),A2)
I keep getting error messeges.

Help

 

[Guest]

Hi,

The following are my observations.
1. You shoud leave a space before and after & to concatenate two strings
2. You have not specified the exact error message !!

 

[Dave Peterson]

Try wrapping that range in =indirect().

=HYPERLINK("#"&CELL("address","'"&Intro!A3&" 1-7'"!A3),A2)
becomes
=HYPERLINK("#"&CELL("address",indirect("'"&Intro!A3&" 1-7'"!A3")),A2)

You're really trying to grab the worksheet name from Intro!A3?

If this doesn't work, describe what's in each of those cells and where you want
the link to point.

 

[Guest]

That is the solution, but the formula actually is:
=HYPERLINK("#"&CELL("address",INDIRECT("'"&Intro!A3&" 1-7'!A3")),A2)
without the quotation marks after the 7

Thanks

Yes exactly. I've been trying to grab the sheet's name from that cell.
maybe you can explain why the regular reference wouldn't work and i would
need the indirect function.

 

[Dave Peterson]

Glad you got it working. (I didn't test it and missed that set of " marks.

=cell() expects a range as that second argument.

The first version you tried used a string--not a range.

Kind of the difference between:
=A1
and
="A1"
the first returns the value in A1. The second just returns the string "A1".

Wrapping a string inside of =indirect() makes excel return the reference (or
range).

 

[Guest]

Now i see. That makes sense.

Thanks for your help.