Checking for a formula

[rrucksdashel]

How can I tell in a cell on one sheet if a cell on another sheet is an input
value or a formula (e.g., 4 or =2*2)? I use the conditional formatting
=(HasFormula) when the cell that I am checking is on the same sheet but
conditional formating won't let me reference another sheet (I don't think)

 

[Peo Sjoblom]

Name the cell on the other sheet (select cell., do insert>name>define)then
refer to the name in the formatting

=HASFORMULA(name_cell)

--
Regards,

Peo Sjoblom

(No private emails please)

 

[David McRitchie]

Instead of referencing a cell as sheet95!e1
you can use a named cell don't know if that will really help you
or not. You could use a helper column on your current sheet.

You will also have a problem if your hasformula is refereenced
from your personal.xls workbook, it will have to be in the same workbook.

--

 

[rrucksdashel]

I get a #REF! when I enter this formula:
=HasFormula(TEST)
The cell TEST had 4 in it and I also had =2+2 in it andin both cases,
#REF!was the answer.

Also, can a cell reference be used instead of a name because the location
will change?

 

[Peo Sjoblom]

You must have named the cell incorrectly, works for me as long as the UDF is
in the same workbook
No, you cannot use a cell reference unless you use a second cell to link to,
assume you put

=HASFORMULA(Sheet2!A2)

in Sheet1!IV1, then from the same sheet check for IV1

=IV1=TRUE

--
Regards,

Peo Sjoblom

(No private emails please)

 

[rrucksdashel]

Maybe you can help me. I have the following:
In a blank workbook, I entered 4 in A1. Then in A2 I entered
=HasFormula (A1)
It gives me this
#NAME?

Also, if I name A1 as "Test" (without the quotes) and change the A1 in A2,
to Test, it gives me the same answer.

I have never been able to get this to work and really need for it to. Also,
you referred to a helper column. What is that and how would that help here?

Instead of referencing a cell as sheet95!e1
you can use a named cell don't know if that will really help you
or not. You could use a helper column on your current sheet.

You will also have a problem if your hasformula is refereenced
from your personal.xls workbook, it will have to be in the same workbook.

--
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

How can I tell in a cell on one sheet if a cell on another sheet is an input
value or a formula (e.g., 4 or =2*2)? I use the conditional formatting
=(HasFormula) when the cell that I am checking is on the same sheet but
conditional formating won't let me reference another sheet (I don't think)

 

[Peo Sjoblom]

HASFORMULA is a UDF and not built in, have you installed a function
called HASFORMULA anywhere?


--

Regards,

Peo Sjoblom

Maybe you can help me. I have the following:
In a blank workbook, I entered 4 in A1. Then in A2 I entered
=HasFormula (A1)
It gives me this
#NAME?

Also, if I name A1 as "Test" (without the quotes) and change the A1 in A2,
to Test, it gives me the same answer.

I have never been able to get this to work and really need for it to. Also,
you referred to a helper column. What is that and how would that help here?

Instead of referencing a cell as sheet95!e1
you can use a named cell don't know if that will really help you
or not. You could use a helper column on your current sheet.

You will also have a problem if your hasformula is refereenced
from your personal.xls workbook, it will have to be in the same workbook.

--
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

How can I tell in a cell on one sheet if a cell on another sheet is an input
value or a formula (e.g., 4 or =2*2)? I use the conditional formatting
=(HasFormula) when the cell that I am checking is on the same sheet but
conditional formating won't let me reference another sheet (I don't

think)

 

[rrucksdashel]

Not to my knowledge. Is that an Add-In? How do I install it?

HASFORMULA is a UDF and not built in, have you installed a function
called HASFORMULA anywhere?


--

Regards,

Peo Sjoblom

Maybe you can help me. I have the following:
In a blank workbook, I entered 4 in A1. Then in A2 I entered
=HasFormula (A1)
It gives me this
#NAME?

Also, if I name A1 as "Test" (without the quotes) and change the A1 in A2,
to Test, it gives me the same answer.

I have never been able to get this to work and really need for it to. Also,
you referred to a helper column. What is that and how would that help here?

Instead of referencing a cell as sheet95!e1
you can use a named cell don't know if that will really help you
or not. You could use a helper column on your current sheet.

You will also have a problem if your hasformula is refereenced
from your personal.xls workbook, it will have to be in the same workbook.

--
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

How can I tell in a cell on one sheet if a cell on another sheet is an input
value or a formula (e.g., 4 or =2*2)? I use the conditional formatting
=(HasFormula) when the cell that I am checking is on the same sheet but
conditional formating won't let me reference another sheet (I don't think)

 

[Peo Sjoblom]

http://www.mvps.org/dmcritchie/excel/formula.htm#hasformula

--

Regards,

Peo Sjoblom

Not to my knowledge. Is that an Add-In? How do I install it?

HASFORMULA is a UDF and not built in, have you installed a function
called HASFORMULA anywhere?


--

Regards,

Peo Sjoblom

Maybe you can help me. I have the following:
In a blank workbook, I entered 4 in A1. Then in A2 I entered
=HasFormula (A1)
It gives me this
#NAME?

Also, if I name A1 as "Test" (without the quotes) and change the A1 in A2,
to Test, it gives me the same answer.

I have never been able to get this to work and really need for it to. Also,
you referred to a helper column. What is that and how would that help here?

:

Instead of referencing a cell as sheet95!e1
you can use a named cell don't know if that will really help you
or not. You could use a helper column on your current sheet.

You will also have a problem if your hasformula is refereenced
from your personal.xls workbook, it will have to be in the same workbook.

--
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"rrucksdashel" <[email protected]> wrote in

message

How can I tell in a cell on one sheet if a cell on another sheet

is an
input

value or a formula (e.g., 4 or =2*2)? I use the conditional formatting
=(HasFormula) when the cell that I am checking is on the same

sheet
but

conditional formating won't let me reference another sheet (I

don't
think)

 

[David McRitchie]

Hi "rrucksdashel" ,
If the function was installed in personal.xls then you would have to
invoke with

=personal.xls!HasFormula(A1)

Better examples for GetFormula earlier on the same page (look for usage.
http://www.mvps.org/dmcritchie/excel/formula.htm#getformula

More information on installling and using macros and user defined functions
http://www.mvps.org/dmcritchie/excel/getstarted.htm
http://www.mvps.org/dmcritchie/excel/install.htm

You don't normally need to include the workbook name when invoking a macro
from a different workbook as long as the workbook is open and hidden.
User Defined Functions are not macros and you have to include the
reference to the workbook when different usually by prefixing with the workbookname,

Excel can only have one workbook open with the same filename.xls
so you don't need the full pathname.

A helper column is a column added a sheet to help with such things as
- other functions, sorting, macros, conditional formatting
Typically it would be a column of the same formula (different arguments)
copied down with the fill hande.

The use of =personal.xls!HasFormula(A1) filled down with the
fill hangel would be an example of a helper column, in this case probably
just to help you visualize what you have rather than to help some other aspect of Excel.
Use of the fill handle is described in
http://www.mvps.org/dmcritchie/excel/fillhand.htm

The term "helper column" was first used, I think, by "ragdyer" on Mon, 8 Apr 2002
and it was raining somewhere in the world on that day.
http://groups.google.com/groups?threadm=#SvEyg33BHA.2660@tkmsftngp04
anyway the term was obvious and quickly caught on, and has been explained
only a handful of times.

BTW, most of us do use our names in the newsgroups. "ragdyer" has stuck to
his handle as being his profession, everyone posting should be able to be
contacted in email to make full use of newsgroups. Like if something comes up
a couple of months. Many people will use a completely different email address
for newsgroups than used elsewhere..

 

[rrucksdashel]

Thank you for your help. I was able to get this to work in a blank workbook.
I have a VERY large and complicated workbook that we are trying to get this
to work in and it would not. We figured that there must have already been a
reference to this name in one of the Add-Ins or something. As such, we tried
"GotFormula(cell reference) and it worked OK when they were input. The
problem is that some of the formulae returned #VALUE! after the workbook was
recalculated. Do you have any idea as to what would be causing this problem?

http://www.mvps.org/dmcritchie/excel/formula.htm#hasformula

--

Regards,

Peo Sjoblom

Not to my knowledge. Is that an Add-In? How do I install it?

HASFORMULA is a UDF and not built in, have you installed a function
called HASFORMULA anywhere?


--

Regards,

Peo Sjoblom


Maybe you can help me. I have the following:
In a blank workbook, I entered 4 in A1. Then in A2 I entered
=HasFormula (A1)
It gives me this
#NAME?

Also, if I name A1 as "Test" (without the quotes) and change the A1 in A2,
to Test, it gives me the same answer.

I have never been able to get this to work and really need for it to.
Also,
you referred to a helper column. What is that and how would that help
here?

:

Instead of referencing a cell as sheet95!e1
you can use a named cell don't know if that will really help you
or not. You could use a helper column on your current sheet.

You will also have a problem if your hasformula is refereenced
from your personal.xls workbook, it will have to be in the same
workbook.

--
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

How can I tell in a cell on one sheet if a cell on another sheet is an
input
value or a formula (e.g., 4 or =2*2)? I use the conditional
formatting
=(HasFormula) when the cell that I am checking is on the same sheet
but
conditional formating won't let me reference another sheet (I don't
think)

 

[David McRitchie]

see my post in this thread about including the workbook the UDF is in
=personal.xls!HasFormula(A1)