| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| CELL Function: cell reference by formula | Excel Worksheet Functions | |||
| numerical integration | Excel Discussion (Misc queries) | |||
| Can function in one cell change value or function in another cell? | Excel Worksheet Functions | |||
| Function that Returns address of that cell? | Excel Worksheet Functions | |||
| Reading Cell Function??? | Excel Worksheet Functions | |||
Linear Mode
