String as Formula without Macro / VBA

[gimpemon]

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!

 

[Niek Otten]

Without VBA you can't enter a string as a formula AFAIK.
Do look at the INDIRECT() function in HELP, which may be all you need.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| 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!

 

[Gary''s Student]

Create two new tabs, alpha and omega; arrange that alpha is ALWAYS the first
sheet and omega is ALWAYS the last sheet. Both sheet will be empty. then:

=SUM(alphamega!K11)

will always encompass all the sheets

 

[gimpemon]

Thanks to both you for your swift replies. I've opted for Gary's
soln, which was the temporary fix I was already using.

Shame the VBA action isn't possible....

Best,
Greg

 

[Excel]

I don't understand how you're defining the range names Division_Names
& Num_Divisions. But I suspect that you may be able to do what you're
trying to do with "Dynamic Range names". Are you familiar with this?