Ask Your Question

Identifying referenced cell address on a different sheet.

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 close merge delete


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
Login/Signup to Answer

Question Tools

1 follower


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

Seen: 76 times

Last updated: Mar 24 '16