Hi,

I'm trying to create a formula dynamically by concatenating a number
of cells into a string which represents the my desired formula,
however, I can't get excel to accept the result as a formula.  Instead
it displays it as a string.

The formula I'm trying to create is a sum across multiple sheets:

=SUM(SC1:OP1!K11)   I.e. Sum K11 on all tabs from SC1 to OP1.

The problem is that SC1 and OP1 may change name and the number of
sheets between them / their order may also change.  I've set up an
array of the sheet names such that the 1st value in the array is the
first sheet to sum from and the last position in the array is the last
sheet.

I'm then concatenating the results as follows:

="=SUM("&INDEX(Division_Names,
1)&":"&INDEX(Division_Names,Num_Divisions)&"!K11)"

Which returns the desired =SUM(SC1:OP1!K11)

HOWEVER, excel reads this as a string, not a formula!!!   It's driving
me mad!!

I know using VBA it's possible easily, something along the lines of:

Sub Formula()
 First_Div = Range("G17").Text   'SC1
 Last_Div = Range("G18").Text   'OP1
 ActiveCell.Formula = "=SUM(" & First_Div & ":" & Last_Div & "!K11)"
End Sub

Unfortunately I'm not allowed macros (don't ask) so what I need is to
be able to tell Excel that the string is in fact a formula (i.e.
replicate the .Formula VBA instruction).

I've seen only a few posts on this, but nothing that seems to work.  I
don't seem to be able to pass a multiple sheet range to the INDIRECT
function either.

=SUM(INDIRECT(INDEX(Division_Names,
1)&":"&INDEX(Division_Names,Num_Divisions)&"!K11"))

Also doesn't seem to work??

Any help more than appreciated!