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

Indirect linking

M

Mudassar.freedi

New Member
Dear Excel ninjas,

I have to trace excel formula =A1 in spread sheets mostly. I can easily go to normal linked cell address using Ctrl+[
but when my colleague use indirect function like =INDIRECT(ADDRESS($A2,I$1,,,$A$1)), i can not go directly on linked address. is there any short way to go.

thanks for your time.
Hafiz Mudassar
 
Hi,

Ctrl+[ would work even if it is referred from other sheet, atleast for cases I tested.

To address your question, can you explain what would be in your $A2 and I$1 cells?

Regards,
Prasad DN
 
Hi Khalid ,

It does not matter ; I think the moment you use the INDIRECT function , the short-cut does not work.

Try it with anything within the INDIRECT function , say :

=INDIRECT($A$2)

where A2 contains the address of any other cell , say the text string J11.

When you press CTRL [ , you will not be taken to J11 ; instead you will be taken to A2.

Similarly , if you use Mudassar's example formula :

=INDIRECT(ADDRESS($A2,I$1,,,$A$1))

pressing CTRL [ will highlight the cells A1 , A2 and I1 , and the result of the entire reference , whether it is on the same sheet or not , will not be highlighted. For instance , if A2 contains 5 , and I1 contains 4 , the address reference is D5 ; even if A1 contains the same sheet name , D5 will not be highlighted.

Narayan
 
NARAYANK991 said:
Hi Khalid ,

It does not matter ; I think the moment you use the INDIRECT function , the short-cut does not work.

Try it with anything within the INDIRECT function , say :

=INDIRECT($A$2)

where A2 contains the address of any other cell , say the text string J11.

When you press CTRL [ , you will not be taken to J11 ; instead you will be taken to A2.

Similarly , if you use Mudassar's example formula :

=INDIRECT(ADDRESS($A2,I$1,,,$A$1))

pressing CTRL [ will highlight the cells A1 , A2 and I1 , and the result of the entire reference , whether it is on the same sheet or not , will not be highlighted. For instance , if A2 contains 5 , and I1 contains 4 , the address reference is D5 ; even if A1 contains the same sheet name , D5 will not be highlighted.

Narayan
Click to expand...

Wow... thats great help.
I've used Ctrl+[ before, but my concept was not much clear.
Specially:
=INDIRECT($A$2) goes to A2 instead of J11. (where A2 contains address string of J11)

Thanks Sir Narayan
 
so,
what to do if i have to use shortcut way to go for linked cell on =INDIRECT(ADDRESS($A2,I$1,,,$A$1)) where
$A2 is column reference
I$1 is Row referece
$A$1 is sheet name (other sheet)

thanks for your time spend.
 
Hi Mudassar ,

I am not sure what is your exact requirement.

The short-cut CTRL [ merely highlights the reference found in a formula ; it does not resolve the formula to arrive at the final reference. This is so even if the formula in question does not use the INDIRECT function.

Suppose , in a cell you have the formula :

=OFFSET($A$2,,3)

What this is doing is referencing the cell 3 columns to the right of cell A2 , which means it is referencing cell D2.

However , if you place the cursor in the cell containing this formula , and press CTRL [ , it will only go to cell A2 , not cell D2.

I do not know whether what you are asking for is possible ; others can help out.

Narayan
 
Dear Narayan,

yes! you are right. i want to go directly to D2 instead of A2 as you mentioned above. is there any short cut..?

I have to cross check an excel file with more that 500+ indirect formulas.
thanks.
 
Hi Mudassar,
In cell you have formula:INDIRECT(ADDRESS($A2,I$1,,,$A$1))
Try:
=Hyperlink("[filename.xlsx]" & "sheetname!" & INDIRECT(ADDRESS($A2,I$1,,,$A$1)),"Go")
Note:
The file should be saved,
File name should be in double quotes "".
The sheetname also can be made dynamic with little tweeqs.
This should do the trick.
Regards,
Prasad DN
PS: Check attached file for reference in sheet2.
 

Attachments

  • hyper.xlsx
    8.9 KB · Views: 0
You must log in or register to reply here.
Back
Top