Forum Discussion

WilsonsConvStore's avatar
WilsonsConvStore
Copper Contributor
Jul 29, 2020
Solved

Excel Formula Problem

I have a worksheet called 'Pay Details' which I am referencing from another worksheet in the same Workbook. I entered ='Pay Details'!A3 in a cell in the other sheet but instead of the data from th...
excel
  • JMB17's avatar
    JMB17
    Jul 29, 2020

    WilsonsConvStore 

     

    Did you confirm that the cell is not formatted as text? Right click, select "number format" from the menu, and I think it should bring up a dialog box. If it's text, change it. Then click in formula bar and hit enter to re-enter the formula.

Mainow's avatar
Mainow
Copper Contributor
Sep 15, 2020

Hi Rajesh_Sinha 

Thanks, but this is not the case.

The two solutions you show are basically the same feature. a shortcut to this is ctrl + `  (the apostrophe, usually above the tab key on a US keyboard)

But that feature applies to all cells in the sheet. This issue is something which happens cell by cell.

IMHO, this is a bug in Excel. If a formula refers to a cell which is formatted as text, the cell with the formatting becomes text formatting as well. That's not a problem, but the next time you edit the cell, the formula shows and not the value.

Rajesh_Sinha's avatar
Rajesh_Sinha
Iron Contributor
Sep 15, 2020

Mainow ,, yes you are write,, in case of Formula only, if starts with Sheet reference  like =Sheet1!A1,  and if the cell is formatted as TEXT,, Excel returns the Formula instead of value, but if the cell has Numeric or Date value, then works properly. 

 

Since the OP hasn't shared the Workbook to examine so that I've suggested two of common  possibilities.

 

 

 

Resources