CODEWITHSUNDEEP
excelexcel-formula
JonWay
JonWay

Reputation: 1735

Convert Cell reference to the Cell Name Not Value

I want to display the reference of cell based on condition that if the cell contains a value it should display that the cell has a value for example If cell A1 has a value eg 12, then cell B1 should display “There is value in cell A1” else it should be blank I have tried this this 

=IF(A1="","","There is value in cell "&A1).

Does anyone know how I can change it to =There is value in cell A1 Instead of There is value in cell 12

Upvotes: 0

Views: 1020

Answers (2)

Imran Malek
Imran Malek

Reputation: 1719

Something like this

=IF(A1="","","There is value in cell "&ADDRESS(ROW(A1),COLUMN(A1),4))

Also if you don't have any issue with the volatility an easier formula would be

=IF(A1="","","There is value in cell "&CELL("address",A1))

P.S You can additionally use one of the four values as the last parameter in the first formula.

enter image description here

Upvotes: 2

zipa
zipa

Reputation: 27899

Does this do the trick:

=IF(A1<>"","There is a value in cell "&CHAR(64+COLUMN(A1))&ROW(A1),"")

You can also use ADDRESS():

=IF(A1<>"","There is a value in cell "&ADDRESS(ROW(A1),COLUMN(A1),4))

It takes row and column as arguments with optional argument for return type, that is set to relative in this example.

Upvotes: 1

Related Questions