Go to cell location from Cell Address formula

[Arts]

Hi all

I think what I am asking is not possible but wanted to confirm this through the excel messiah's.

I am using the CELL formula to locate the the cell address so CELL(ADDRESS(...which all works fine.

Is there an excel workbook option to then click on the result so say I get the result C25 to then take me to that cell rather than having to type it in the name box (top left) or use the CTRL G function.

Many thanks as always
Arts

 

[FormR]

You could investigate the HYPERLINK() function - for example:

=HYPERLINK("#"&CELL("address",C25))

 

[Arts]

You could investigate the HYPERLINK() function - for example:

=HYPERLINK("#"&CELL("address",C25))

Thank you for the suggestion!! I had no idea HYPERLINK could be used as a function.

My formula is such =IFERROR(SUBSTITUTE(CELL("address",INDEX($C$14:$C$203,MATCH($B$11,$C$14:$C$203,FALSE))),"$",""),"")

(SUBSTITUTE was added to remove the "$" as CELL(ADDRESS returns with absolute references)

for simplicity I trimmed it down to =CELL("address",INDEX($C$14:$C$203,MATCH($B$11,$C$14:$C$203,FALSE))) in order to wrap the hyperlink around it though am having issues.

Any assistance would be hugely appreciated wrapping the hyperlink around this.

Arts

 

[FormR]

Hi, you didn't show what you tried - but give this a go.

=HYPERLINK("#"&CELL("address",INDEX($C$14:$C$203,MATCH($B$11,$C$14:$C$203,FALSE))))

You shouldn't need to worry about the absolute references, unless it's for aesthetic reasons.

 

[Arts]

Hi, you didn't show what you tried - but give this a go.

=HYPERLINK("#"&CELL("address",INDEX($C$14:$C$203,MATCH($B$11,$C$14:$C$203,FALSE))))

You shouldn't need to worry about the absolute references, unless it's for aesthetic reasons.

Apologies I didn't show what I tried but I've literally been spending the last few hours trying to crack it and after a lot of research came out with the below which seems to work.

=HYPERLINK(CONCATENATE("#",CELL("address",INDEX($C$14:$C$203,MATCH($B$11,$C$14:$C$203,FALSE)))))


I was told (I say told whilst researching that CONCATENATE had to be used but will give your formula a whirl right now!

Thanks
Arts

 

[Arts]

Hi, you didn't show what you tried - but give this a go.

=HYPERLINK("#"&CELL("address",INDEX($C$14:$C$203,MATCH($B$11,$C$14:$C$203,FALSE))))

You shouldn't need to worry about the absolute references, unless it's for aesthetic reasons.

Thank you so much for providing this, does exactly what I need!

Arts

 

[FormR]

Thank you so much for providing this, does exactly what I need!

I was told (I say told whilst researching that CONCATENATE had to be used

FYI - the ampersand character (&) can be used as a short hand way to concatenate strings rather than using the CONCATENATE() function.

 

[Arts]

FYI - the ampersand character (&) can be used as a short hand way to concatenate strings rather than using the CONCATENATE() function.

Noted, thank you once again.

Arts

 

[Arts]

Noted, thank you once again.

Arts

If I was to replace the index function with a VLOOKUP function instead this seems to throw up an error. Does the VLOOKUP not work with the CELL(ADDRESS function?

Quite strange....

 

[FormR]

Does the VLOOKUP not work with the CELL(ADDRESS function?

No it doesn't - VLOOKUP() just returns the value being looked-up. INDEX() when used in the way it is with the Cell("Address"..) function is returning the range being indexed.

If you want to display the results of the VLOOKUP() formula in the cell rather than the address then you can put it in the "friendly_name" argument of the HYPERLINK() function.

i.e.

=HYPERLINK("#"&CELL("address",INDEX($C$14:$C$203,MATCH($B$11,$C$14:$C$203,FALSE))),VLOOKUP(.....))