Show cell value in formula instead of cell reference

[Sufiyan97]

Is it possible to show cell value instead of cell reference in a formula? expected results are in column D

Formula (1).xlsx
	A	B	C	D
1	Compensation			Expected
2	1294.23	-52.2	1346.43	1346.43
3	1219.23	-52.2	1271.43	1271.43
4	1219.23	-52.2	1271.43
5	1301.86	-52.2	1354.06
6	1219.23	-52.2	1271.43
7	1305.67	-52.2	1357.87
8	1219.23	-53.66	1272.89
9	1212.16	-53.66	1265.82
10	1240.76	-53.66	1294.42
11	1223.5	-53.66	1277.16
12	1219.23	-53.66	1272.89
13	1219.23	-53.66	1272.89
14	1232.43	-53.66	1286.09
15	1219.23	-53.66	1272.89
16	1231.73	-53.66	1285.39
17	973.99	-53.66	1027.65
18	1099.87	-53.66	1153.53
19	1268.63	-53.66	1322.29
20	1033.58	-53.66	1087.24
21	22953.02	903.46	22049.56
22
Sheet2

Cell Formulas
Range	Formula
D2	D2	=1294.23+52.2
D3	D3	=1219.23+52.2
C2:C21	C2	=A2-B2

this might help by Fluff

Convert Part of Formula as values

Is there any way to convert B2 formula as =6977835.66/1000 and so on ABC1OriginalDivide By 1000Expected...

www.mrexcel.com

 

[DanteAmor]

Is this what you want?

Dante Amor
	A	B	C	D
1	Compensation			Expected
2	1294.23	-52.2	1346.43	=1294.23+52.2
3	1219.23	-52.2	1271.43	=1219.23+52.2
4	1219.23	-52.2	1271.43	=1219.23+52.2
5	1301.86	-52.2	1354.06	=1301.86+52.2
6	1219.23	-52.2	1271.43	=1219.23+52.2
7	1305.67	-52.2	1357.87	=1305.67+52.2
8	1219.23	-53.66	1272.89	=1219.23+53.66
9	1212.16	-53.66	1265.82	=1212.16+53.66
10	1240.76	-53.66	1294.42	=1240.76+53.66
11	1223.5	-53.66	1277.16	=1223.5+53.66
12	1219.23	-53.66	1272.89	=1219.23+53.66
13	1219.23	-53.66	1272.89	=1219.23+53.66
14	1232.43	-53.66	1286.09	=1232.43+53.66
15	1219.23	-53.66	1272.89	=1219.23+53.66
16	1231.73	-53.66	1285.39	=1231.73+53.66
17	973.99	-53.66	1027.65	=973.99+53.66
18	1099.87	-53.66	1153.53	=1099.87+53.66
19	1268.63	-53.66	1322.29	=1268.63+53.66
20	1033.58	-53.66	1087.24	=1033.58+53.66
21	22953.02	903.46	22049.56	=22953.02-903.46
Hoja6

Cell Formulas
Range	Formula
C2:C21	C2	=A2-B2
D2:D21	D2	="="&A2&IF(B2<0,"+"&-B2,"-"&B2)

 

[Sufiyan97]

Sorry @DanteAmor It is not, I want final result to show up in cell, while the result in your post in column D should show up in formula bar.

it should show cell value instead of cell reference.

Just look at column D expected results in my post.

 

[DanteAmor]

I'm not understanding.
Instead of a minisheet you could put an image to know what you want to see in the cell and what you want to see in the formula bar.

Since in the answer to which you refer, this is what I see in the cell (=6977835.66/1000) and in the formula bar there is a formula:

So in my response in the cell is observed (=1294.23+52.2 ) and in the formula bar a formula:

----------------------------------
That is why my confusion.

If you could put a picture, maybe it will be clearer for me.

 

[Peter_SSs]

this might help by Fluff

I'm not sure why you have provided that link. It seems to me that you were effectively asking the same question in that thread as you are here and you marked Fluff's post as the solution yet it did not do what your expected result showed in that thread. Dante's formula here does the same thing as Fluff's formula in that other thread.

Is it possible to show cell value instead of cell reference in a formula? expected results are in column D

I am happy to be proved wrong but I don't believe that what you are asking is possible**

**That is unless you would be happy to have your existing formula in C2, manually go to the formula bar and select A2 in that formula and press F9 and then select B2 in the formula bar and press F9 again. In that case you would then see this in the formula bar

Note though that if you do this and then alter a value in A2 or B2 the formula in C2 will not update to reflect the new A2/B2 value(s)

 

[Sufiyan97]

Hi @DanteAmor and @Peter_SSs I am sorry for the confusion,

Below is what I expecte as result, which Peter mentioned that need to do manually by pressing F9 in each cell, I want if there is any way to do it in one go.

Current formula in formula bar,

Expected Result in formula bar while keeping cells result same as current formula that is 1346.43, so basically it is to show cell value in formula bar instead of cell reference.

Thanks for both of your time and apologize for confusion.

 

[DanteAmor]

I don't know if it can be done with a formula but here is an option with a macro:
Change "D" on this line
With Range("D" & i)
By "C" or by the column where you want the result.

Sub ExpectedResult()
  Dim i As Long
  For i = 2 To Range("A" & Rows.Count).End(3).Row
    With Range("D" & i)
      If Range("B" & i).Value < 0 Then
        .Formula = "=" & Range("A" & i).Value & "+" & -Range("B" & i).Value
      Else
        .Formula = "=" & Range("A" & i).Value & "-" & Range("B" & i).Value
      End If
    End With
  Next
End Sub

 

[Sufiyan97]

Thank you very much Dante, That is it.

Thank you for your time and help.

 

[Peter_SSs]

That is it.

I don't know anything about your data or reason for wanting to do this but remember that if the values in column A or B change, the formula won't automatically reflect the new value(s)

 

[Sufiyan97]

I don't know anything about your data or reason for wanting to do this but remember that if the values in column A or B change, the formula won't automatically reflect the new value(s)

Yes Peter I know that, but I keep getting strange requests from colleagues and they get it from their clients.

Thanks for the comment.