Ask Your Question

Identifying referenced cell address on a different sheet. [closed]

asked 2016-03-23 23:17:35 +0200

David gravatar image

Using Calc, I have a cell on one sheet that pulls its string contents from a different sheet. For illustration:

  • Sheet One in A1 has =$'other sheet'.D2;
  • Other Sheet in D2 has "My string".

On yet a third sheet, I want to know what column is being referenced in $'sheet one'.A1 -- that is, whatever formula/function is used, the third sheet should get the value: D.

Question: How to extract that "D" value from "Sheet One" on "Sheet Three"??

(When I've made an attempt using MID() plus a "soft" cell reference, it just treats the cross reference as the string: it doesn't analyse the "returned" value in that cell.)

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2020-09-02 11:00:07.239426


By the way: What good for? (I am curious, you should know.)

Lupp gravatar imageLupp ( 2016-03-24 11:02:53 +0200 )edit

@Lupp - I am modifying a spreadsheet that came from a third party with password-protected cells. Only certain cells will permit input. I'm creating a sheet which makes it easier for the user to manage data, but it means that I have restrictions on what cells I can use on the pre-existing sheets. Thus my (odd!) question. :)

David gravatar imageDavid ( 2016-03-24 13:04:43 +0200 )edit

1 Answer

Sort by » oldest newest most voted

answered 2016-03-24 10:44:00 +0200

Lupp gravatar image

If you can assure a one-letter-column you may use =MID(FORMULA(Sheet1.A1),FIND(".",FORMULA(Sheet1.A1))+1,1) .
For more details see attached!

edit flag offensive delete link more


Thanks, Lupp! :) Your attachment also includes the solution for double-character columns, which my "use case" will certainly include. That's brilliant!

David gravatar imageDavid ( 2016-03-24 12:58:04 +0200 )edit

Question Tools

1 follower


Asked: 2016-03-23 23:17:35 +0200

Seen: 156 times

Last updated: Mar 24 '16