Refer to formula in another cell (not value) 2

[RobTsintas]

Should be a quick question. What's the function to refer to another cell's formula?

eg. A1 has: =IF(1=1,"Hello","Goodbye")

So the value of A1 is "Hello"

In B1, I want to return the formula in A1 (or part of it, using left/right/mid). Like this:

=LEFT(FORMULA(A1),5)

This should return "=IF(1"

(There is no such function as FORMULA, but what is the correct function?)

Thanks

 

[SkipVought]

Hi,

Copy this function to a MODULE in your VBA project (alt+F11) or into a MODULE in your PERSONAL.XLS workbook

Function WhatFormula(rng As Range) As String
    WhatFormula = rng.Formula
End Function

Use as you might any other spreadsheet function.

What, may I ask, is the purpose of this?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[RobTsintas]

Thanks. I'll give it a go. I'm surprised there's no built-in function though.

Since you ask, here's the reason I need to get at the formula:

I received a spreadsheet that has lots of cells which use IF conditions on the cells next to them to return TRUE/FALSE, like so:

=IF(B1="Turnips","TRUE","FALSE") =IF(B2="Oranges","TRUE","FALSE")
=IF(B3="Bananas","TRUE","FALSE")
and so on.

I wanted to extract the required values into a list without having to manually copy and paste the Turnips, Oranges and Bananas out of each formula.

So I figured if I can use a function to get at the formula, in combination with a couple of FIND functions, I can strip out the fruit and copy this formula down to get my list.

Yes, I could have done the whole lot in VBA, but didn't really want to get that involved.

Cheers

 

[SkipVought]

Your multiple embedded IF statements and HIDDEN DATA, would be MUCH better replaced by a LIST and a MATCH function.

BTW, you can use the Edit > Find feature to locate stuff in formulas as well.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[RobTsintas]

I agree.

I wouldn't have built the sheet like this, but as I say, that's how I received it. That's also why my first step was to try to extract the hidden data using this function!


It doesn't seem to be working though. I've used VBA quite a lot, but never to create custom functions before, so maybe I'm doing something wrong...

I copied your code above into a new module ("Module3") in my PERSONAL.XLS. Then I saved and restarted Excel just to make sure. But trying to use the WhatFormula function in a sheet anywhere just gives me a #NAME? error.

I've also tried putting Public before Function.

Any ideas?

 

[SkipVought]

Select the function from the Insert > Function dialog. If it's in your PERSONAL.XLS, then THAT must be used as well in the formula syntax.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[RobTsintas]

Aha, so it needs to be PERSONAL.XLS!WhatFormula

It works now.

Thanks!

 

[SkipVought]

It is almost ALWAYS best to SELECT functions, references etc.

Then the only TYPING you have to do is literals and operators.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[xlhelp]

please see my post in thread68-1566004 and change the named formula to
=GET.CELL(6,OFFSET(Sheet1!C4,-1,0,1,1))
where c3 contains the formula

and type a formula in cell C4
=LEFT(FormuIs,6)
I used 6 to get "=IF(C2"

A man has only two choices: He can be right or he can be happy.

 

[SkipVought]

xlhelp, I must have missed your earlier contribution.

==> [purple]*[/purple]

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}