Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula vs. Constant
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula vs. Constant
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" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula vs. Constant
thanks but i need a function so i can use it in a formula setting.
regards, RK "FSt1" wrote: 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" wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula vs. Constant
"rk0909" wrote:
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 ----- "rk0909" wrote: thanks but i need a function so i can use it in a formula setting. regards, RK "FSt1" wrote: 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" wrote: 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula vs. Constant
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. -- Biff Microsoft Excel MVP "rk0909" wrote in message ... thanks but i need a function so i can use it in a formula setting. regards, RK "FSt1" wrote: 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" wrote: 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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula vs. Constant
"T. Valko" wrote:
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. Biff, regardless of RK's answer, could you explain what you have in mind, for my edification. ----- original message ----- "T. Valko" wrote in message ... 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. -- Biff Microsoft Excel MVP "rk0909" wrote in message ... thanks but i need a function so i can use it in a formula setting. regards, RK "FSt1" wrote: 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" wrote: 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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula vs. Constant
You can use the Excel macro function GET.CELL.
See this: http://groups.google.com/group/micro...cf859ecd?tvc=2 -- Biff Microsoft Excel MVP "Joe User" <joeu2004 wrote in message ... "T. Valko" wrote: 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. Biff, regardless of RK's answer, could you explain what you have in mind, for my edification. ----- original message ----- "T. Valko" wrote in message ... 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. -- Biff Microsoft Excel MVP "rk0909" wrote in message ... thanks but i need a function so i can use it in a formula setting. regards, RK "FSt1" wrote: 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" wrote: 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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula vs. Constant
"T. Valko" wrote:
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" wrote in message ... You can use the Excel macro function GET.CELL. See this: http://groups.google.com/group/micro...cf859ecd?tvc=2 -- Biff Microsoft Excel MVP "Joe User" <joeu2004 wrote in message ... "T. Valko" wrote: 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. Biff, regardless of RK's answer, could you explain what you have in mind, for my edification. ----- original message ----- "T. Valko" wrote in message ... 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. -- Biff Microsoft Excel MVP "rk0909" wrote in message ... thanks but i need a function so i can use it in a formula setting. regards, RK "FSt1" wrote: 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" wrote: 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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula vs. Constant
Works for me in both Excel 2002, 2007
-- Biff Microsoft Excel MVP "Joe User" <joeu2004 wrote in message ... "T. Valko" wrote: 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" wrote in message ... You can use the Excel macro function GET.CELL. See this: http://groups.google.com/group/micro...cf859ecd?tvc=2 -- Biff Microsoft Excel MVP "Joe User" <joeu2004 wrote in message ... "T. Valko" wrote: 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. Biff, regardless of RK's answer, could you explain what you have in mind, for my edification. ----- original message ----- "T. Valko" wrote in message ... 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. -- Biff Microsoft Excel MVP "rk0909" wrote in message ... thanks but i need a function so i can use it in a formula setting. regards, RK "FSt1" wrote: 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" wrote: 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 |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula vs. Constant
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 "Joe User" <joeu2004 wrote in message ... "T. Valko" wrote: 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" wrote in message ... You can use the Excel macro function GET.CELL. See this: http://groups.google.com/group/micro...cf859ecd?tvc=2 -- Biff Microsoft Excel MVP "Joe User" <joeu2004 wrote in message ... "T. Valko" wrote: 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. Biff, regardless of RK's answer, could you explain what you have in mind, for my edification. ----- original message ----- "T. Valko" wrote in message ... 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. -- Biff Microsoft Excel MVP "rk0909" wrote in message ... thanks but i need a function so i can use it in a formula setting. regards, RK "FSt1" wrote: 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" wrote: 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 |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula vs. Constant
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula vs. Constant
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" wrote: 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 "Joe User" <joeu2004 wrote in message ... "T. Valko" wrote: 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" wrote in message ... You can use the Excel macro function GET.CELL. See this: http://groups.google.com/group/micro...cf859ecd?tvc=2 -- Biff Microsoft Excel MVP "Joe User" <joeu2004 wrote in message ... "T. Valko" wrote: 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. Biff, regardless of RK's answer, could you explain what you have in mind, for my edification. ----- original message ----- "T. Valko" wrote in message ... 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. -- Biff Microsoft Excel MVP "rk0909" wrote in message ... thanks but i need a function so i can use it in a formula setting. regards, RK "FSt1" wrote: 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" wrote: 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 . |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula vs. Constant
That is so cute. Excellent stuff!
Bob "Lori Miller" wrote in message ... 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" wrote: 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 "Joe User" <joeu2004 wrote in message ... "T. Valko" wrote: 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" wrote in message ... You can use the Excel macro function GET.CELL. See this: http://groups.google.com/group/micro...cf859ecd?tvc=2 -- Biff Microsoft Excel MVP "Joe User" <joeu2004 wrote in message ... "T. Valko" wrote: 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. Biff, regardless of RK's answer, could you explain what you have in mind, for my edification. ----- original message ----- "T. Valko" wrote in message ... 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. -- Biff Microsoft Excel MVP "rk0909" wrote in message ... thanks but i need a function so i can use it in a formula setting. regards, RK "FSt1" wrote: 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" wrote: 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 . |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula vs. Constant
"Bob Phillips" wrote:
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" wrote in message ... 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 "Joe User" <joeu2004 wrote in message ... "T. Valko" wrote: 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" wrote in message ... You can use the Excel macro function GET.CELL. See this: http://groups.google.com/group/micro...cf859ecd?tvc=2 -- Biff Microsoft Excel MVP "Joe User" <joeu2004 wrote in message ... "T. Valko" wrote: 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. Biff, regardless of RK's answer, could you explain what you have in mind, for my edification. ----- original message ----- "T. Valko" wrote in message ... 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. -- Biff Microsoft Excel MVP "rk0909" wrote in message ... thanks but i need a function so i can use it in a formula setting. regards, RK "FSt1" wrote: 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" wrote: 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 |
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula vs. Constant
"Joe User" <joeu2004 wrote in message ... "Bob Phillips" wrote: 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. |
#16
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula vs. Constant
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,TEXT REF("rc")) ,1,9,"choose(3"))) "Bob Phillips" wrote in message ... That is so cute. Excellent stuff! Bob "Lori Miller" wrote in message ... 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" wrote: 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 "Joe User" <joeu2004 wrote in message ... "T. Valko" wrote: 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" wrote in message ... You can use the Excel macro function GET.CELL. See this: http://groups.google.com/group/micro...cf859ecd?tvc=2 -- Biff Microsoft Excel MVP "Joe User" <joeu2004 wrote in message ... "T. Valko" wrote: 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. Biff, regardless of RK's answer, could you explain what you have in mind, for my edification. ----- original message ----- "T. Valko" wrote in message ... 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. -- Biff Microsoft Excel MVP "rk0909" wrote in message ... thanks but i need a function so i can use it in a formula setting. regards, RK "FSt1" wrote: 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" wrote: 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 . |
#17
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula vs. Constant
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. -- Biff Microsoft Excel MVP "Lori Miller" wrote in message ... 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" wrote: 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 "Joe User" <joeu2004 wrote in message ... "T. Valko" wrote: 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" wrote in message ... You can use the Excel macro function GET.CELL. See this: http://groups.google.com/group/micro...cf859ecd?tvc=2 -- Biff Microsoft Excel MVP "Joe User" <joeu2004 wrote in message ... "T. Valko" wrote: 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. Biff, regardless of RK's answer, could you explain what you have in mind, for my edification. ----- original message ----- "T. Valko" wrote in message ... 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. -- Biff Microsoft Excel MVP "rk0909" wrote in message ... thanks but i need a function so i can use it in a formula setting. regards, RK "FSt1" wrote: 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" wrote: 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 . |
#18
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula vs. Constant
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 "T. Valko" wrote in message ... 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. -- Biff Microsoft Excel MVP "Lori Miller" wrote in message ... 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" wrote: 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 "Joe User" <joeu2004 wrote in message ... "T. Valko" wrote: 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" wrote in message ... You can use the Excel macro function GET.CELL. See this: http://groups.google.com/group/micro...cf859ecd?tvc=2 -- Biff Microsoft Excel MVP "Joe User" <joeu2004 wrote in message ... "T. Valko" wrote: 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. Biff, regardless of RK's answer, could you explain what you have in mind, for my edification. ----- original message ----- "T. Valko" wrote in message ... 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. -- Biff Microsoft Excel MVP "rk0909" wrote in message ... thanks but i need a function so i can use it in a formula setting. regards, RK "FSt1" wrote: 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" wrote: 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 . |
#19
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula vs. Constant
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 "Bernd P" wrote: Hello, You can use Get.Cell: http://sulprobil.com/html/get_cell.html Regards, Bernd . |
#20
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula vs. Constant
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(TE XTREF("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. -- Biff Microsoft Excel MVP "Bob Phillips" wrote in message ... 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 "T. Valko" wrote in message ... 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. -- Biff Microsoft Excel MVP "Lori Miller" wrote in message ... 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" wrote: 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 "Joe User" <joeu2004 wrote in message ... "T. Valko" wrote: 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" wrote in message ... You can use the Excel macro function GET.CELL. See this: http://groups.google.com/group/micro...cf859ecd?tvc=2 -- Biff Microsoft Excel MVP "Joe User" <joeu2004 wrote in message ... "T. Valko" wrote: 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. Biff, regardless of RK's answer, could you explain what you have in mind, for my edification. ----- original message ----- "T. Valko" wrote in message ... 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. -- Biff Microsoft Excel MVP "rk0909" wrote in message ... thanks but i need a function so i can use it in a formula setting. regards, RK "FSt1" wrote: 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" wrote: 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 . |
#21
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula vs. Constant
Not sure I'm following you.
This is not a problem if you put the formula **outside** of the referenced ranges. For example, this array formula** : =AVERAGE(IF(C1:C10=6,IF(D1:D10="Germany",F1:F10))) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. As long as you don't enter that formula anywhere within C1:D10, F1:F10 it will do what you want. -- Biff Microsoft Excel MVP "rk0909" wrote in message ... 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 "Bernd P" wrote: Hello, You can use Get.Cell: http://sulprobil.com/html/get_cell.html Regards, Bernd . |
#22
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula vs. Constant
thanks again. However, this formula needs to be applied in the range itself
and hats why I am getting a circular reference. This needs to be applied to a large matrix so I wanted to do it within the range itself and not create a separate range. thanks again for prompt responses. RK "T. Valko" wrote: Not sure I'm following you. This is not a problem if you put the formula **outside** of the referenced ranges. For example, this array formula** : =AVERAGE(IF(C1:C10=6,IF(D1:D10="Germany",F1:F10))) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. As long as you don't enter that formula anywhere within C1:D10, F1:F10 it will do what you want. -- Biff Microsoft Excel MVP "rk0909" wrote in message ... 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 "Bernd P" wrote: Hello, You can use Get.Cell: http://sulprobil.com/html/get_cell.html Regards, Bernd . . |
#23
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula vs. Constant
"rk0909" wrote:
thanks again. However, this formula needs to be applied in the range itself and hats why I am getting a circular reference. [....] Need a formula which averages all of col F with 6 in Col C and Germany in Col D .....And you want the average formula to be in a cell somewhere in the middle of, say column F? Would one of the following approaches work for you? Suppose the data are in rows 1 through 100, and you want the average formula in row 50. Then try either of the following array formulas[*]: 1. =average(if(c1:c49=6,if(d1:d49="Germany",f1:f49)), if(c51:c100=6,if(d51:d100="Germany",f51:f100))) The ranges should adjust automagically if you insert or delete rows above and below the cell with this formula. 2. Under Tools Options Calculation (in Excel 2003), set the Iteration option and Maximum Iterations to 1. Then: =average(if(c1:c100=6,if(d1:d100="Germany",f1:f100 ))) Again, an array formula is committed by pressing ctrl+shift+Enter instead of just Enter. You should see curly braces around the entire formula in the Formula Bar, viz. {=formula}. But you should not type the curly braces yourself. If you make a mistake, select the cell and press F2, make modifications if any, then press ctrl+shift+Enter. ----- original message ----- "rk0909" wrote: thanks again. However, this formula needs to be applied in the range itself and hats why I am getting a circular reference. This needs to be applied to a large matrix so I wanted to do it within the range itself and not create a separate range. thanks again for prompt responses. RK "T. Valko" wrote: Not sure I'm following you. This is not a problem if you put the formula **outside** of the referenced ranges. For example, this array formula** : =AVERAGE(IF(C1:C10=6,IF(D1:D10="Germany",F1:F10))) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. As long as you don't enter that formula anywhere within C1:D10, F1:F10 it will do what you want. -- Biff Microsoft Excel MVP "rk0909" wrote in message ... 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 "Bernd P" wrote: Hello, You can use Get.Cell: http://sulprobil.com/html/get_cell.html Regards, Bernd . . |
#24
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula vs. Constant
In response to your questions...
TEXTREF is like INDIRECT but also evaluates expressions in RC notation. The idea is to change 1 to 0 and evaluate: IF(0,IsFormula,A1) for use in the second argument of Get.Cell. Since null arguments evaluate to 0 you can omit the 0 in IF(). Similarly the second approach evaluates: choose(2,Get.Cell,48,A1) and choose(3,Get.Cell,48,A1) for use in both Get.Cell arguments. As you probably know for debugging you can open an Excel4 Macro sheet and enter these expressions using the formula wizard then press ctrl+` to change to values. Another application is for evaluating expressions. Define a name "Evaluate" to refer to: =EVALUATE(EVALUATE(REPLACE(GET.CELL(6,TEXTREF("rc" )),1,5,"if("))) then you can try things like: =IF(1,Evaluate,"sum("&A1&")") where A1 contains comma separated values. As Biff says, this is really aimed at users for exposing macro functionality without udfs. Udfs make more sense from a developer standpoint: Function GetCell(Type_Num As Integer, Reference As Range) As Variant GetCell = ExecuteExcel4Macro("get.cell(" & Type_Num & _ "," & Reference.Address(True, True, xlR1C1, True) & ")") End Function "T. Valko" wrote in message ... 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(TE XTREF("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. -- Biff Microsoft Excel MVP "Bob Phillips" wrote in message ... 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 "T. Valko" wrote in message ... 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. -- Biff Microsoft Excel MVP "Lori Miller" wrote in message ... 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" wrote: 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 "Joe User" <joeu2004 wrote in message ... "T. Valko" wrote: 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" wrote in message ... You can use the Excel macro function GET.CELL. See this: http://groups.google.com/group/micro...cf859ecd?tvc=2 -- Biff Microsoft Excel MVP "Joe User" <joeu2004 wrote in message ... "T. Valko" wrote: 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. Biff, regardless of RK's answer, could you explain what you have in mind, for my edification. ----- original message ----- "T. Valko" wrote in message ... 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. -- Biff Microsoft Excel MVP "rk0909" wrote in message ... thanks but i need a function so i can use it in a formula setting. regards, RK "FSt1" wrote: 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" wrote: 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 . |
#25
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula vs. Constant
this works if there I use only one formula per column. If there are more
than one cells where i need to insert this formula, I get circ reference. Is there a way to structure the If statement that skips the cells with formulas or zeros. Then I can average across without getting into the circ problem. thanks again for all your help. "Joe User" wrote: "rk0909" wrote: thanks again. However, this formula needs to be applied in the range itself and hats why I am getting a circular reference. [....] Need a formula which averages all of col F with 6 in Col C and Germany in Col D ....And you want the average formula to be in a cell somewhere in the middle of, say column F? Would one of the following approaches work for you? Suppose the data are in rows 1 through 100, and you want the average formula in row 50. Then try either of the following array formulas[*]: 1. =average(if(c1:c49=6,if(d1:d49="Germany",f1:f49)), if(c51:c100=6,if(d51:d100="Germany",f51:f100))) The ranges should adjust automagically if you insert or delete rows above and below the cell with this formula. 2. Under Tools Options Calculation (in Excel 2003), set the Iteration option and Maximum Iterations to 1. Then: =average(if(c1:c100=6,if(d1:d100="Germany",f1:f100 ))) Again, an array formula is committed by pressing ctrl+shift+Enter instead of just Enter. You should see curly braces around the entire formula in the Formula Bar, viz. {=formula}. But you should not type the curly braces yourself. If you make a mistake, select the cell and press F2, make modifications if any, then press ctrl+shift+Enter. ----- original message ----- "rk0909" wrote: thanks again. However, this formula needs to be applied in the range itself and hats why I am getting a circular reference. This needs to be applied to a large matrix so I wanted to do it within the range itself and not create a separate range. thanks again for prompt responses. RK "T. Valko" wrote: Not sure I'm following you. This is not a problem if you put the formula **outside** of the referenced ranges. For example, this array formula** : =AVERAGE(IF(C1:C10=6,IF(D1:D10="Germany",F1:F10))) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. As long as you don't enter that formula anywhere within C1:D10, F1:F10 it will do what you want. -- Biff Microsoft Excel MVP "rk0909" wrote in message ... 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 "Bernd P" wrote: Hello, You can use Get.Cell: http://sulprobil.com/html/get_cell.html Regards, Bernd . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Constant in Excel Formula | Excel Worksheet Functions | |||
Writing a formula with a constant | Excel Discussion (Misc queries) | |||
Set a constant from a formula in VBA | Excel Discussion (Misc queries) | |||
Formula Constant | Excel Worksheet Functions | |||
Constant Value in formula. | Excel Worksheet Functions |