De-link a cell reference in a formula.

[Hari Prasadh]

Hi,

This is a little different compared to my earlier post.

Cell A1 has the value Qupcode
Cell B1 has the value ID
Cell B2 has the value 200034
Cell C2 has the value Fret

In Cell F2 I want to DISPLAY the value If ( ID = 200034 ) Qupcode = Fret
So, one can write the formula in Cell F2 as = "if ( " & B1 & " = " & B2 &
" ) " & A1 & " = " & C2

Now, Im writing this formula programmatically. So, I wrote the following
formula.

Cells(2, "f").Formula = "= ""if( "" & b1 & "" = """ & " & " & Cells(z,
"b").Address & " & "") "" & " & Cells(1, "a").Address & " & "" = "" & " &
Cells(z, "c").Address

If I go back to excel , it DISPLAYS the result as wanted.

Now, if I go in to editing mode then the formula is

= "if( " & B1 & " = " & $B$2 & ") " & $A$1 & " = " & $C$2

I have one requirement in the above. In the EDITING mode I prefer the actual
value in Cell(1,1) which is Qupcode rather than A1 or $A$1.

That is I want, the formula not to have any reference to Cell(1,1) but only
its value to be present.

How to accomplish the same programmatically?

Thanks a lot,
Hari
India

 

[Tom Ogilvy]

Cells(2, "f").Formula = "= ""if( "" & b1 & "" = """ & _
" & " & Cells(z, "b").Address & " & "") "" & """ _
& Cells(1, "a").Value & """ & "" = "" & " & Cells(z, "c").Address

produces
= "if( " & B1 & " = " & $B$2 & ") " & "Qupcode" & " = " & $C$2

in Edit Mode and displays:

if( ID = 200034) Qupcode = Fret

 

[Hari Prasadh]

Hi Tom,

Thnx a ton for your help.

Would get up and running.

Thanks a lot,
Hari
India