MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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))...)