Formel to the specific cell - not to the value

[Albireo]

Suppose I have a column (A) of data.
Column (C) contains formulas for column (A)
In this example, only the value from Column (A) is replicated in Column (C).

The easiest way to achieve my primary desire is to e.g. in cell C3 type the formula =A3
But…

Suppose the content of cell A3 is the text “Test Value”, I got the same text in cell C3
If I cut the value in cell A3 and paste it in cell B3, cell A3 becomes empty, but cell C3 still has the text “Test Value”

My wish is that cell C3 still shows the value in cell A3 (in this example, cell C3 should no longer show anything, because cell A3 is now empty)

Tried using INDEX or INDIRECT, but without success.
Any suggestion?

[Villeroy]

ask112576.ods (15.7 KB)

[Albireo]

Thank you!

In my calc I got the formula (in column C) ==INDEX(A:A;ROW())

Later, the cells will be compared. Then I take the opportunity to change the contents of all cells to text, with this formula .: ==CONCATENATE("";INDEX(A:A;ROW()))

[Albireo]

A problem occurred.
Desired cell is not in the same row.

for example Cell C3 should display Cell A2
What will the formula be then?

[karolus]

@Albireo

C3 should display Cell A2

…ROW()-1