Error in cell function
 

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

Error in cell function
 

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 !!

"Jared" wrote:

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

Error in cell function
 

The error is that excel is not accepting the formula. I have tried adding the
spaces like you suggested. No success. It seems as if i cannot put a cell
reference in a formula which will represent a sheets name

"Madhan" wrote:

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 !!

"Jared" wrote:

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

Error in cell function
 

Try wrapping that range in =indirect().

=HYPERLINK("#"&CELL("address","'"&Intro!A3&" 1-7'"!A3),A2)
becomes
=HYPERLINK("#"&CELL("address",indirect("'"&Intro!A 3&" 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.

Jared wrote:

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

--

Dave Peterson

Error in cell function
 

That is the solution, but the formula actually is:
=HYPERLINK("#"&CELL("address",INDIRECT("'"&Intro!A 3&" 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" wrote:

Try wrapping that range in =indirect().

=HYPERLINK("#"&CELL("address","'"&Intro!A3&" 1-7'"!A3),A2)
becomes
=HYPERLINK("#"&CELL("address",indirect("'"&Intro!A 3&" 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.

Jared wrote:

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

--

Dave Peterson

Error in cell function
 

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).


Jared wrote:

That is the solution, but the formula actually is:
=HYPERLINK("#"&CELL("address",INDIRECT("'"&Intro!A 3&" 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" wrote:

Try wrapping that range in =indirect().

=HYPERLINK("#"&CELL("address","'"&Intro!A3&" 1-7'"!A3),A2)
becomes
=HYPERLINK("#"&CELL("address",indirect("'"&Intro!A 3&" 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.

Jared wrote:

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

--

Dave Peterson


--

Dave Peterson

Error in cell function
 

Now i see. That makes sense.

Thanks for your help.


"Dave Peterson" wrote:

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).


Jared wrote:

That is the solution, but the formula actually is:
=HYPERLINK("#"&CELL("address",INDIRECT("'"&Intro!A 3&" 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" wrote:

Try wrapping that range in =indirect().

=HYPERLINK("#"&CELL("address","'"&Intro!A3&" 1-7'"!A3),A2)
becomes
=HYPERLINK("#"&CELL("address",indirect("'"&Intro!A 3&" 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.

Jared wrote:

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

--

Dave Peterson


--

Dave Peterson