Identifying referenced cell address on a different sheet

[dajare]

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

[Lupp]

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

[dajare]

@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.

[Lupp]

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!
ask67151GetColumnPartFromDirectReference001.ods

[dajare]

Thanks, Lupp! Your attachment also includes the solution for double-character columns, which my “use case” will certainly include. That’s brilliant!