Formula to refer to letter on top of column

[jeshkhol]

I need a formula to use in the cells of column A in Sheet2. I suspect (and hope) it is pretty straightforward but I fail to find a way to do it.

The formula should refer to the letters on top of each column of Sheet1 (that is, it should refer to the letters above the cells).

So cell A1 should refer to the letter on top of column A in Sheet1, A2 to the letter on top of column B in Sheet1, A3 to the letter on top of column C in Sheet1, etc., so that column A displays the letters A, B, C, etc.

What formula can I use in column A of Sheet2 to achieve this?
function-column.ods (11.8 KB)

LO 7.4.4.2
macOS 12.6.2

[JohnSUN]

See here Apache OpenOffice Community Forum - Suppress Printed Row Numbers On Wide Spreadsheet - (View topic)
use ROW() instead COLUMN()

=SUBSTITUTE(ADDRESS(1;ROW();4);"1";"")
and
=INDIRECT("$Sheet1."&A1&1)
or just
=OFFSET($Sheet1.$A$1;0;ROW()-1)

function-column.ods (10.0 KB)

[jeshkhol]

Thank you! In fact, it does work for me if I do use COLUMN()

=SUBSTITUTE(ADDRESS(1,COLUMN($Sheet1.A:A),4),"1","")