Help with building formulas
Posted by Ben Posaner on February 01, 2001 3:50 AM
I'm probably being dense but is it possible to build a macro using text/numbers from cells.
I.e. A1 contains the text "AW" & A2 contains "1-50"
And I want b1 to extract "AW","1"&"50"and put into a forula.
e.g. =if(AW1:AW50) etc.
Sorry for confusing anybody!!
Thanks
Posted by Dave Hawley on February 01, 2001 6:08 AM
Hi Ben
Yes you can, the INDIRECT and Text functions will do it in a worksheet formula. Using your example you could use:
=SUM(INDIRECT(A1&LEFT(A2,FIND("-",A2)-1)&":"&A1&MID(A2,FIND("-",A2)+1,LEN(A2))))
Dave
OzGrid Business Applications
Posted by Mark W. on February 01, 2001 8:05 AM
Or more simple put...
=IF(INDIRECT(A1&SUBSTITUTE(B1,"-",":"&A1))...)
Posted by Mark W. on February 01, 2001 8:07 AM
Oops! I can't read...use this instead
=IF(=INDIRECT(A1&SUBSTITUTE(A2,"-",":"&A1))...)
Posted by Mark W. on February 01, 2001 8:09 AM
Oops! Nor can I paste...
=IF(INDIRECT(A1&SUBSTITUTE(A2,"-",":"&A1))...)
