Ask Your Question
0

Calc - How to get cell coordinates as Sheet.A1

asked 2017-02-04 16:59:53 +0200

lovecraft22 gravatar image

Dear all, I need a way to get a selected cell coordinates in this form: =function(Sheet.A1) should provide Sheet.A1

Is there any way to achieve this? From what I can understand =address would not return what I need.

Thanks

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
0

answered 2017-02-04 17:45:09 +0200

karolus gravatar image

Hallo
so far … your question makes no sense, because =Sheet.A1 provides already the content of Sheet.A1

edit flag offensive delete link more

Comments

I don't want it to return the content of Sheet.A1 but I need it to return "Sheet.A1" instead. I need this to list different values and the cell where those values occur in 2 columns, such as

Sheet.A1 =Sheet.A1

Sheet.A2 =Sheet.A2

...

...

lovecraft22 gravatar imagelovecraft22 ( 2017-02-04 18:59:18 +0200 )edit

So, may be you mean something like this? =SUBSTITUTE(RIGHT(CELL("FILENAME");LEN(CELL("FILENAME"))-FIND("#$";CELL("FILENAME"))-1)&"."&CELL("ADDRESS";A1);"$";"") Just replace A1 to real reference

JohnSUN gravatar imageJohnSUN ( 2017-02-04 19:42:08 +0200 )edit

Thanks! Works fine however it only works correctly if the cell where you fill the formula in is in the same sheet as cell A1, otherwise I get sheet2.sheet3.A1. Is there any way to fix it? (I'm not quite sure I understand how it works exactly)

lovecraft22 gravatar imagelovecraft22 ( 2017-02-04 19:53:05 +0200 )edit

Got it! =CELL("ADDRESS";Sheet2.A1) does exactly what I need! Thanks for your insight!

lovecraft22 gravatar imagelovecraft22 ( 2017-02-04 20:12:51 +0200 )edit

This will not return the sheetname but only the celladdress if the referenced cell is in the current sheet. =CELL("ADDRSSS";Sheet1.A1) will only return $A$1 if called from Sheet1.

Lupp gravatar imageLupp ( 2017-02-04 20:27:13 +0200 )edit

True, but in my case that will be just fine as I'm referencing this from a different sheet.

lovecraft22 gravatar imagelovecraft22 ( 2017-02-04 20:47:16 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2017-02-04 16:59:53 +0200

Seen: 268 times

Last updated: Feb 04 '17