Formula vs. Constant

[rk0909]

Is there a way using excel funtions (not UDF) to find if a cell contains a
formula vs. a number (constant).
I looked around the forum but did not find anything. I tried using cell
function and left function but they return the value in the formula itself.
Any help is appreciated.
Regards,
RK

 

[FSt1]

hi
you might consider the goto function.
press F5. click the special button bottom right.
check formula then ok. excel will high light all formulas.
you can do the same with constrants.

regards
FSt1

 

[rk0909]

thanks but i need a function so i can use it in a formula setting.
regards,
RK

 

[Joe User]

thanks but i need a function so i can use it
in a formula setting.

I don't believe there is any Excel function that distinguishes between a
formula and a constant in another cell.

But then again, I cannot imagine why you would need to make that distinction
in an Excel formula per se, other than some kind of internal validation.

Perhaps if you tell us the real problem you want to solve, we might be able
to offer viable solutions using Excel functions per se.


----- original message -----

 

[T. Valko]

thanks but i need a function so i can use it in a formula setting.

You can do this without VBA code but it depends on how you intend to use
it/do it.

Do you want to identify cells that contain formulas and highlight them with
conditional formatting? Or, some other use?

Need more details.

 

[Joe User]

You can do this without VBA code but it depends on how you
intend to use it/do it.

Biff, regardless of RK's answer, could you explain what you have in mind,
for my edification.


----- original message -----

 

[T. Valko]

You can use the Excel macro function GET.CELL.

See this:

http://groups.google.com/group/micr...c/browse_thread/thread/6bf323d2cf859ecd?tvc=2

 

[Joe User]

You can use the Excel macro function GET.CELL.

Does not seem to work in my version of Excel 2003 (SP3).

Is that an XL2007-ism?


----- original message -----

 

[T. Valko]

Works for me in both Excel 2002, 2007

 

[Bob Phillips]

It works perfectly well in Excel 2003 (11.8316.8221) SP3.

Problem with that approach is that you have to create a defined name to hold
the GET.CELL function, and you cannot pass a cell reference to a defined
name (well you can, but I find it far too difficult to setup and it is
flaky), you have to hardcode that cell into the formula in the name. This
does provide a little flexibility if you use relative cell addressing but
nowhere near enough that you get with a UDF.

The best way IMO is to wrap it as XLM, although the OP may consider this as
no more than VBA

GetCell
=ARGUMENT("Ref",8)
=ARGUMENT("Type",1)
=RETURN(GET.CELL(Type,Ref))
=RETURN()

and call like

=GetCell(A1,48)

HTH

Bob

 

[Bernd P]

Hello,

You can use Get.Cell:
http://sulprobil.com/html/get_cell.html

Regards,
Bernd

 

[Lori Miller]

Instead of the udf =IsFormula(A1), you can enter

=IF(1,IsFormula,A1)

and define the name "IsFormula" to refer to:

=GET.CELL(48,TEXTREF(REPLACE(GET.FORMULA(TEXTREF("rc")),1,5,"if(")))

It's a bit convoluted but not too flaky

 

[Bob Phillips]

That is so cute. Excellent stuff!

Bob

Instead of the udf =IsFormula(A1), you can enter

=IF(1,IsFormula,A1)

and define the name "IsFormula" to refer to:

=GET.CELL(48,TEXTREF(REPLACE(GET.FORMULA(TEXTREF("rc")),1,5,"if(")))

It's a bit convoluted but not too flaky

 

[Joe User]

It works perfectly well in Excel 2003 (11.8316.8221) SP3.
Problem with that approach is that you have to create a
defined name to hold the GET.CELL function

Yup! That was my mistake: not following Biff's instruction to the letter.

First, I tried GET.CELL directly in a formula instead of creating the named
formula. Second, I neglected to select A1 before using the relative
reference A1 in the named formula.

But I don't find the relative reference very practical for RK's purpose,
namely to use in a formula unless you deviate from Biff's instructions. For
example, selecting B1 and defining the named formula =GET.CELL(48,A1)
permits us to apply the named formula to any cell to the left.

Perhaps Lori's approach is more flexible. I haven't tried it.


----- original message -----

 

[Bob Phillips]

But I don't find the relative reference very practical for RK's purpose,
namely to use in a formula unless you deviate from Biff's instructions.
For example, selecting B1 and defining the named formula =GET.CELL(48,A1)
permits us to apply the named formula to any cell to the left.

It isn't as I pointed out earlier. My XLM macro is far better ... but

Perhaps Lori's approach is more flexible. I haven't tried it.

It is, it is sheer brilliance.

 

[Lori Miller]

Thanks Bob, happy to share it. But the main credit should
go to JK Pieterse who i believe had the underlying idea.

A more general approach is: =CHOOSE(1,Get.Cell,48,A1)

Get.Cell: =GET.CELL(EVALUATE(REPLACE(GET.CELL(6,TEXTREF("rc"))
,1,9,"choose(2")),EVALUATE(REPLACE(GET.CELL(6,TEXTREF("rc"))
,1,9,"choose(3")))

 

[T. Valko]

Excellent!

That solves the problem of passing a relative cell ref as Bob noted.

Now I just have to figure out how it works! Not familiar with TEXTREF. I do
have the macrofun help file but I guess I'll have to read it about 10 times
before I understand.

 

[Bob Phillips]

I get all of it ... except the bit where it replaces the first 5 characters,
=IF(1, by IF(.

Why does it do that Lori?

Bob

 

[rk0909]

thanks for all the posts. The problem at hand is:

Col C Col D Col F
6 India 100
6 Germany Need a formula which averages all of col F with 6
in Col C and Germany in Col D
3 China 50
6 Germany Need a formula which averages all of col F with
6 in Col C and Germany in Col D

so i was thinking if there was a way to identfy cells with formula vs.
constant i could use sumifs to avoid circularity.

thanks,

RK

 

[T. Valko]

I'm also having a hard time trying to figure that out.

An alternative that seems a bit easier to understand:

IsFormula
Refers to:

=GET.CELL(48,TEXTREF(SUBSTITUTE(MID(GET.FORMULA(TEXTREF("rc")),17,100),")","")))

It looks like TEXTREF is somewhat similar to INDIRECT.

To those who might be following this thread, the basic idea is that the 2
two formulas are essentially a "circular reference" between the 2 formulas.

If C1 contains this formula:

=IF(1,IsFormula,AB200)

TEXTREF(SUBSTITUTE(MID(GET.FORMULA(TEXTREF("rc")),17,100),")","")) "simply"
extracts the cell ref from the formula in C1 and passes that cell ref to
GET.CELL as a relative R1C1 reference.

The whole trick to getting this to work is coming up with a simple enough
formula like =IF(1,IsFormula,AB200) that can be taken apart to get the cell
reference yet at the same time make some logical sense to the user as to
what the formula is intended to do.

Thank you Lori for giving me a reason to tinker with this!

See, you learn something new every day.