Question again.
Jorge
A1=+20 and A2=+30:
B1= 'A1
the formula to apply
C1= =INDIRECT (B1)
the result= 20
why?
becouse : in the cell B1 exist """"an address"""""
Questons?
have Excel 2000 a Function that can
in the cell B1 exist something like this: (FORMULA)
B1='A1*B1
the principal formula to apply again
C1= =INDIRECT(B1)
the result will be 600
why?
becouse in the cell B1 exist a formula that "Excel INDIRECT function" have
to be evaluate
when I tried, this value had error this function.
more difficult is when in the cell exist a function...for example
B1='=if(A1=30,A1,A1*A2)
and when I create he cell
C1= = INDIRECT(B1)
the result will be: 600
and more more more difiicult!!!!!
when exist
Z1:1, Z2:2
B1=CONCATENATE("A",text(Z1,"0"),"*","B",text(Z2,"0"))
or
B1=CONCATENATE("+A",text(Z1,"0"),"*","B",text(Z2,"0"))
or
B1=CONCATENATE("'+A",text(Z1,"0"),"*","B",text(Z2,"0"))
and I have
C1= =INDIRECT(B1)
the result will be 600
why ???????
becouse is the multiplication of
A1*B1
20*30
= 600
when I tried, this value had error.
thanks for your answers
Jorge Cardenass
pd
I think that using INDIRECT function is not the real function for use in
this case.
maybe exist a Function that EVALUATE the formula(in text) that exist in a
indirect CELL
Tom Ogilvy
Jorge <jcarde...@hotmail.com> wrote in message
news:OsZ$UCER$GA....@cppssbbsa02.microsoft.com...
> I have a basic and simple idea about that I have to explain
>
> A1=+20 and A2=+30:
> B1= 'A1
>
> the formula to apply
> C1= =INDIRECT (B1)
> the result= 20
>
> why?
> becouse : in the cell B1 exist """"an address"""""
>
> Questons?
> have Excel 2000 a Function that can
> in the cell B1 exist something like this: (FORMULA)
> B1='A1*B1
>
> the principal formula to apply again
> C1= =INDIRECT(B1)
> the result will be 600
> why?
> becouse in the cell B1 exist a formula that "Excel INDIRECT function"
have
> to be evaluate
> when I tried, this value had error this function.
If you want the product of A1*A2 in C1 then in C1 put
=A1*A2
if you want C1 to have the value of A2 times the value of the cell specified
in B1 in C1 put
=Indirect(B1)*A2
Where B1 has A1 (you don't need a single quote)
>
>
> more difficult is when in the cell exist a function...for example
> B1='=if(A1=30,A1,A1*A2)
> and when I create he cell
> C1= = INDIRECT(B1)
> the result will be: 600
Indirect won't work with B1 if B1 has the text string (not formula)
if(A1=30,A1,A1*A2) or even if it has '=if(A1=30,A1,A1*A2)
If you want to build a formula dynamically, you can build the references
dynamically, but not the Operators in the formula.
if C1 has the formula:
=if(Indirect(B1)=30,Indirect(b1),Indirect(b1)*A2)
it will return 600 if cell B1 contains the string A1
> and more more more difiicult!!!!!
> when exist
> Z1:1, Z2:2
>
> B1=CONCATENATE("A",text(Z1,"0"),"*","B",text(Z2,"0"))
> or
> B1=CONCATENATE("+A",text(Z1,"0"),"*","B",text(Z2,"0"))
> or
> B1=CONCATENATE("'+A",text(Z1,"0"),"*","B",text(Z2,"0"))
>
> and I have
> C1= =INDIRECT(B1)
> the result will be 600
> why ???????
> becouse is the multiplication of
> A1*B1
> 20*30
> = 600
> when I tried, this value had error.
Same reason as above
C1:
=INDIRECT(CONCATENATE("A",TEXT(Z1,"0")))*INDIRECT(CONCATENATE("A",TEXT(Z2,"0
")))
returns 600. you don't need to involve B1. Using the & as the concatenation
operator is simpler and no reason to explicitely use the Text function -
excel will do the conversion:
=Indirect("A"&Z1)*Indirect("A"&Z2) works just as well.
>
>
> thanks for your answers
> Jorge Cardenass
> pd
> I think that using INDIRECT function is not the real function for use in
> this case.
> maybe exist a Function that EVALUATE the formula(in text) that exist in a
> indirect CELL
Indirect function is the correct function to use to use Indirect cell
references (thus its name). You can turn it into an EVALUATE function
however - because that is not what it does. You need to set up the formula
so you don't have to have an evaluate formula and you can get the results
using indirect which I have showed you how to do.
Regards,
Tom Ogilvy
MVP Excel
>
>
Alan Linton
<jcarde...@hotmail.com> writes
>I have a basic and simple idea about that I have to explain
>
>A1=+20 and A2=+30:
>B1= 'A1
>
>the formula to apply
>C1= =INDIRECT (B1)
>the result= 20
>
>why?
>becouse : in the cell B1 exist """"an address"""""
>
>Questons?
>have Excel 2000 a Function that can
>in the cell B1 exist something like this: (FORMULA)
>B1='A1*B1
>
>the principal formula to apply again
>C1= =INDIRECT(B1)
>the result will be 600
>why?
>becouse in the cell B1 exist a formula that "Excel INDIRECT function" have
>to be evaluate
>when I tried, this value had error this function.
>
>
>B1='=if(A1=30,A1,A1*A2)
>and when I create he cell
>C1= = INDIRECT(B1)
>the result will be: 600
>
>when exist
>Z1:1, Z2:2
>
>B1=CONCATENATE("A",text(Z1,"0"),"*","B",text(Z2,"0"))
>or
>B1=CONCATENATE("+A",text(Z1,"0"),"*","B",text(Z2,"0"))
>or
>B1=CONCATENATE("'+A",text(Z1,"0"),"*","B",text(Z2,"0"))
>
>and I have
>C1= =INDIRECT(B1)
>the result will be 600
>why ???????
>becouse is the multiplication of
>A1*B1
>20*30
>= 600
>when I tried, this value had error.
>
>
>Jorge Cardenass
>pd
>I think that using INDIRECT function is not the real function for use in
>this case.
>maybe exist a Function that EVALUATE the formula(in text) that exist in a
>indirect CELL
>
>
VBA provides an Evaluate(string) function that does what you want but it
is not directly accessible from the worksheet. To make it accessible you
can write a VBA eval function like this :-
Function eval(func As String)
Application.Volatile
eval = Evaluate(func)
End Function
I made the function volatile because it does not always know when it
needs to recalculate. A volatile function is recalculated whenever
calculation occurs in any cell on the worksheet. This is not efficient
but it works.
Here are some examples of the eval function in action.
I am using Excel 97, SR2a. I think this should work in Excel 2000.
A B C D E
-------------------------------------------------
1 20 30 A1 20 =eval(C1)
2 20 30 A2*B2 600 =eval(C2)
3 20 30 IF(A3=30,A3,A3*B3) 600 =eval(C3)
4 30 30 IF(A4=30,A4,A4*B4) 30 =eval(C4)
5 1 1 "A"&A5&"*"&"B"&B5 A1*B1 =eval(C5)
6 1 1 "A"&A6&"*"&"B"&B6 600 =eval(eval(C6))
7 sin(pi()/4) 0.7071 =eval(C7)
I just thought of this today and the tests shown above are all the
testing I have done so I won't be surprised if someone picks holes in
this.
See the VBA built-in help for more information on Evaluate(String).
Hope this helps.
--
Alan Linton
Harlan Grove
news:3DULrEAS...@cranley.demon.co.uk...
<snip>
> VBA provides an Evaluate(string) function that does what you want but it
> is not directly accessible from the worksheet. To make it accessible you
> can write a VBA eval function like this :-
>
> Function eval(func As String)
> Application.Volatile
> eval = Evaluate(func)
> End Function
As an alternative, Laurent Longre's MOREFUNC.XLL add-in provides an EVAL
function doing the same thing as the code above, but because it's an XLL
(compiled) add-in rather than a VBA module it should run faster.
MOREFUNC.XLL is freely available at
http://perso.wanadoo.fr/longre/excel/pages/Downloads.htm#Morefunc.xll
Harlan Grove
news:3856E7A2...@free.fr...
> Harlan,
>
> Harlan Grove a écrit :
> >
> > MOREFUNC.XLL is freely available at
> >
> > http://perso.wanadoo.fr/longre/excel/pages/Downloads.htm#Morefunc.xll
>
> is http://longre.free.fr/english
>
> And if you speak French <g> : http://longre.free.fr
Un peu, un très petit peu. Merci.
And the 'free' in the url - is l'Académie française going to make you eat
Big Macs and fish & chips for the rest of your natural life?
Laurent Longre
Harlan Grove a écrit :
>
> MOREFUNC.XLL is freely available at
>
> http://perso.wanadoo.fr/longre/excel/pages/Downloads.htm#Morefunc.xll
I've just moved my pages to another server. The new (and shorter!) URL
is http://longre.free.fr/english
And if you speak French <g> : http://longre.free.fr
Laurent
Laurent Longre
>
> And the 'free' in the url - is l'Académie française going to make you eat
> Big Macs and fish & chips for the rest of your natural life?
... and Coca-Cola ? Mille fois non !!!
Mais les ISP français ("Freesbee", "Wanadoo", "Skynet", "LibertySurf",
"Free"...) semblent considérer que la langue anglaise est
consubstancielle à l'Internet. Nous autres, simples utilisateurs, n'y
pouvons rien.
Laurent