CODEWITHSUNDEEP
excelexcel-formula
Regis Santos
Regis Santos

Reputation: 3749

Excel: Formula Returning from conditional

How to return formula of other cell (column L) if choose item in column A. Example: if answer, then return =I5 & " " & J5 if foo, then return '=I7 & " " & J7 & " " & K7 & " x " & L7

I want to return the formula instead of the result. enter image description here

Upvotes: 0

Views: 169

Answers (2)

Brad
Brad

Reputation: 12253

Put this function in a public module

Function GetFormula(rng As Range) As Variant
    GetFormula = rng.Formula
End Function

I think this is what you want. There are several options for how you want that formula returned such as R1C1 style.

Edit

Oh I think I get what you want more. Ok to apply the formula you have in your blue table to whatever cell you want in column F you can call the Indirect function.

So for example if you want to apply the formula '=I7 & " " & J7 & " " & K7 & " x " & L7 to your column F then call =INDIRECT(I7 & " " & J7 & " " & K7 & " x " & L7). In your column I don't include the = sign. it will make it a bit easier.

You can use Vlookup to find where this should be applied by using your lookup item in column A in your reference table (the blue table). Return the formula in column I.

Upvotes: 1

danielpiestrak
danielpiestrak

Reputation: 5439

You can achieve this by nesting your formulas.

put this in E5

=IF(A5 = "answer", A5 & " " & B5 & " " & D5, 
 IF(A5 = "bar", A5 & " " & B5 & " " & C5, 
 IF(A5 = "foo", A5 & " " & B5,
 "No If Formula Here yet")))

I started the formula off with the first two formatting's for you.

Upvotes: 0

Related Questions