sheet formulas and strings

[Robert Bencomo]

Hi!.... this is a two part newbie question:

1) if any cell has the following data: "sheet" & A1
(where cell A1 contains an integeter value), does Excel merge the
string and integer value into a single STRING??

For example, if A1 holds an int value of 3, will Excel convert
the entire expression into a string value of "sheet3"???


2) Does excel have a function that will convert a string value into
a sheet reference??? I seem to be having problems with the
following formula:

("sheet" & A1)!F1 + F2

Basically, my sheets are labeled sheet1, sheet2 and sheet3, and the
above
formula is supposed to select a sheet based on the contents of cell A1.
Can
("sheet" & A1) somehow be converted to a sheet pointer or something??

Hope that wasn't too confusing! Thanks so much!

 

[Rick Rothstein \(MVP - VB\)]

1) No, concatenating two pieces of text results in text, not a cell
reference.

2) You can use the INDIRECT function to convert a text string (of a cell
reference) into the actual cell reference itself. The formula you would want
to add F2 on your current sheet (where the formula is) with F1 on the sheet
with the name "Sheet" concatenated with the contents of A1 (on the sheet
where the formula is) would be this one...

=INDIRECT("sheet"&A1&"!F1")+F2

Rick

 

[Robert Bencomo]

Great thanks! I was hoping that this would work on "Excel Mobile" on my
version
of Windows Mobile 5.0.... however, the program does not seem to have
this INDIRECT() function.... Darn! 8(

Oh well, at least I can try it on my regular version of Excel on my Windows
2000
machine.