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

 

[Arts]

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

It's never straight forward is it, just when you think you've worked out one thing up pops another aspect to get your head around .

I've always thought INDEX(MATCH was an alternative to VLOOKUP in order to look to the left. Also they would both return the same result ie substitute VLOOKUP with INDEX MATCH and the outcome would be that of the same (result)

 

[Arts]

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

Would you be able to explain this a bit more please ? Where I am is that both would return the same value ie if I replaced a vlookup with Index match the (assume the vlookup is working how it should) the result will be the same so why would in the case in the CELL address would the vlookup not gel with CELL(ADDRESS function. I assumed the CELL ADDRESS is being picked up of the result of the lookup.

Arts

 

[FormR]

In some situations INDEX() will return a reference to a cell rather than the cells value. VLOOKUP() only returns the cells value.

For example - this sums the values in the range A1:A10:

=SUM(INDEX(A:A,1):INDEX(A:A,10))

You can't do the same thing with VLOOKUP()

 

[Arts]

In some situations INDEX() will return a reference to a cell rather than the cells value. VLOOKUP() only returns the cells value.

For example - this sums the values in the range A1:A10:

=SUM(INDEX(A:A,1):INDEX(A:A,10))

You can't do the same thing with VLOOKUP()

Thanks for the extra input, I'll be honest in that the penny hasn't dropped yet not seeing what you're explaining.

I'm only concentrating on the output which is where I'm more than likely tripping up. In the below example both are retuning 65, where you have mentioned Index returns a reference I'm not "seeing" the reference but see the output which in this case is 65. (hope that makes sense in the lens I'm viewing it)

In some situations INDEX() will return a reference to a cell rather than the cells value. VLOOKUP() only returns the cells value.

For example - this sums the values in the range A1:A10:

=SUM(INDEX(A:A,1):INDEX(A:A,10))

You can't do the same thing with VLOOKUP()

Even with the above you mention INDEX returns a reference but the above would still return a value.

I guess in short I'm not understanding the difference between a reference and a value based on the above examples....

 

[FormR]

I guess in short I'm not understanding the difference between a reference and a value based on the above examples....

In those examples both INDEX() and VLOOKUP() are returning the cells value.

The point is that INDEX() can return a reference to the cell instead. When it's used in the CELL() function it's clever enough to know that it needs a cell reference and returns that instead of the cells value.

Try using the "evaluate formula" function from the "formulas" tab to "evaluate these two formulas:

Book1
	A	B	C
1	some text		$A$1
2			some text
Sheet1

Cell Formulas
Range	Formula
C1	C1	=CELL("address",INDEX(A:A,1))
C2	C2	=INDEX(A:A,1)

You'll see that the INDEX() function when inside the CELLS() function returns a cell reference A1 - not the text "A1" but the actual cell reference. Where as the second formula returns the contents on the cell.

 

[Arts]

In those examples both INDEX() and VLOOKUP() are returning the cells value.

The point is that INDEX() can return a reference to the cell instead. When it's used in the CELL() function it's clever enough to know that it needs a cell reference and returns that instead of the cells value.

Try using the "evaluate formula" function from the "formulas" tab to "evaluate these two formulas:

Book1
	A	B	C
1	some text		$A$1
2			some text
Sheet1

Cell Formulas
Range	Formula
C1	C1	=CELL("address",INDEX(A:A,1))
C2	C2	=INDEX(A:A,1)

You'll see that the INDEX() function when inside the CELLS() function returns a cell reference A1 - not the text "A1" but the actual cell reference. Where as the second formula returns the contents on the cell.

I've said this a lot today but once again thank you soo much for this. This helps a lot.

 

[Arts]

Hi all rather then start a new post as it was related to this thought this would be the best way to post.

The formula provided earlier worked but had a quick query, is it possible within the hyperlink aspect of the formula to show the link location and the "friendly name"

The reason I say this is I have "Press here" which takes you to the cell but within the spreadsheet it can be hard to see where the cursor is (due to different coloured cells) so was wondering if it was possible to show "Press here" and then have #$C$10 so in all so you can see what row you should be looking too at first glance.

"Press Here #$C$10"

Hope that makes sense

Arts

 

[FormR]

Hi, you can just add the CELL(..) part to the friendly name argument.

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

 

[Arts]

Hi, you can just add the CELL(..) part to the friendly name argument.

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

That works perfectly! Thank you so much!!

I was adding the cell address after the formula ie =HYPERLINK("#"&CELL("address",INDEX($C$14:$C$203,MATCH($B$11,$C$14:$C$203,FALSE)))) & Cell address.....