Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Checking for a formula
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) |
#2
|
|||
|
|||
Checking for a formula
Name the cell on the other sheet (select cell., do insertnamedefine)then
refer to the name in the formatting =HASFORMULA(name_cell) -- Regards, Peo Sjoblom (No private emails please) "rrucksdashel" 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) |
#3
|
|||
|
|||
Checking for a formula
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" 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) |
#4
|
|||
|
|||
Checking for a formula
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" wrote: Name the cell on the other sheet (select cell., do insertnamedefine)then refer to the name in the formatting =HASFORMULA(name_cell) -- Regards, Peo Sjoblom (No private emails please) "rrucksdashel" 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) |
#5
|
|||
|
|||
Checking for a formula
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" wrote in message ... 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" wrote: Name the cell on the other sheet (select cell., do insertnamedefine)then refer to the name in the formatting =HASFORMULA(name_cell) -- Regards, Peo Sjoblom (No private emails please) "rrucksdashel" 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) |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Checking for a formula
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? "David McRitchie" wrote: 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" 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) |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Checking for a formula
HASFORMULA is a UDF and not built in, have you installed a function
called HASFORMULA anywhere? -- Regards, Peo Sjoblom "rrucksdashel" wrote in message ... 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? "David McRitchie" wrote: 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" 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) |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Checking for a formula
Not to my knowledge. Is that an Add-In? How do I install it?
"Peo Sjoblom" wrote: HASFORMULA is a UDF and not built in, have you installed a function called HASFORMULA anywhere? -- Regards, Peo Sjoblom "rrucksdashel" wrote in message ... 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? "David McRitchie" wrote: 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" 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) |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Checking for a formula
http://www.mvps.org/dmcritchie/excel...htm#hasformula
-- Regards, Peo Sjoblom "rrucksdashel" wrote in message ... Not to my knowledge. Is that an Add-In? How do I install it? "Peo Sjoblom" wrote: HASFORMULA is a UDF and not built in, have you installed a function called HASFORMULA anywhere? -- Regards, Peo Sjoblom "rrucksdashel" wrote in message ... 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? "David McRitchie" wrote: 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" 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) |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Checking for a formula
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...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?thre...%40tkmsftngp04 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.. --- 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 "Peo Sjoblom" wrote in message ... http://www.mvps.org/dmcritchie/excel...htm#hasformula "rrucksdashel" wrote... 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? |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Checking for a formula
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? "Peo Sjoblom" wrote: http://www.mvps.org/dmcritchie/excel...htm#hasformula -- Regards, Peo Sjoblom "rrucksdashel" wrote in message ... Not to my knowledge. Is that an Add-In? How do I install it? "Peo Sjoblom" wrote: HASFORMULA is a UDF and not built in, have you installed a function called HASFORMULA anywhere? -- Regards, Peo Sjoblom "rrucksdashel" wrote in message ... 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? "David McRitchie" wrote: 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" 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) |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Checking for a formula
see my post in this thread about including the workbook the UDF is in
=personal.xls!HasFormula(A1) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
adding row to forumla | Excel Discussion (Misc queries) | |||
dragging a formula | Excel Discussion (Misc queries) | |||
Simplify formula | Excel Worksheet Functions | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) | |||
Cell doesn't show formula result - it shows formula (CTRL + ' doe. | Excel Worksheet Functions |