• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

dynamic link to cell contents of a different worksheet

K

khal

New Member
Hi All,
I thought this would be a trivial thing but NOT! I'm trying to create a dynamic link to a cell in a different worksheet within the same workbook. For example the different worksheet name is 'Data' so normally if I wanted cell content A29 it would be =Data!A29. Simple enough. But I want to dynamically reference the cell portion of the Data!xx address.

All help appreciated!
 
Hi, @khal!

You can use:
=HYPERLINK("#'data'!"&A1)
or with a "friendly name":
=HYPERLINK("#'data'!"&A1,"friendly name")

A1: Cell with text reference (Example --> A29)

P.S.: if your sheet name not have spaces, you can ignore aphostrophes:
=HYPERLINK("#data!"&A1)

Blessings!
 
Thanks, that indeed creates a hyperlink but it does not get the content. However let me rephrase or add to I my question, I'm trying to retrieve the contents of the Data!A29, I haven't found a way to change the cell A29 reference dynamically. I tried concantenating using Data!&A29 and the concantenate function but that gets an error.
 
John Jairo V said:
If you want to get the content, just use INDIRECT function:

=INDIRECT("'Data'!"&A1)
A1: Cell with text reference (Example --> A29)

Blessings!
Click to expand...

Thanks John! I always have trouble with the " ' syntax, is there a guide somewhere that explains it?
Thanks again.
 
Hi ,

John has already mentioned that the single quote or apostrophe (irrespective of the terminology , this is the character with the ASCII code 39 decimal) , is required only if the worksheet tab name has space or special characters in it ; otherwise , you can dispense with the apostrophe.

The double quotes are required for the INDIRECT function itself , because its parameter has to be a string.

Suppose you have a formula in cell B1 , which is =A1 ; this displays in cell B1 the contents of cell A1.

To obtain the same output as the above , using the INDIRECT function , you can have :

=INDIRECT("A1")

or , enter the text string A1 in cell C1 , and in cell D1 , enter the formula :

=INDIRECT(C1)

Narayan
 
You must log in or register to reply here.
Back
Top