Replacing Sheet name in formula

[Ron McCormick]

I have a sheet with formulae in the format:

=SUM(OFFSET('Centre 1'!$F17,,('Centre 1'!F$8-1)*12):OFFSET('Centre
1'!$F17,,F$8*12-1)),

the purpose of which is to sum monthly figures into annual ones. I want to
replace 'Centre 1' with another centre anything from 1 - 10. I have a drop
down box in a cell named CentreNameChoice.

I was trying to do it via VBA using something like:

Sub CentreSummaryChoice()
'
' CentreSummaryChoice Macro
' Macro recorded 21/07/2006 by Ron McCormick
'

'
Range("CentreNameInput").Select
ActiveCell.FormulaR1C1 = "=""'""&CentreNameChoice&""'"""
Range("CentreNameInput").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("TenYearSummary").Select
Cells.Replace What:="'Centre??'", Replacement:=CentreNameInput, LookAt:= _
xlPart, SearchOrder:=xlByColumns, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False
End Sub

but it does not seem to work.

Is there and Excel function that I can use directly in my Excel formula,
that will remove the need for the macro or can someone advise how I need to
amend my code.

Thanks

Ron

 

[Jim Thomlinson]

You can use the indirect function directly in your Excel Formula something
like this...

=SUM(OFFSET(Indirect("'Centre " & CentreNameInput &
"'!$F17"),,(Indirect("'Centre " & CentreNameInput & "'!$F8")
-1)*12):OFFSET(Indirect("'Centre " & CentreNameInput & "'!$F17"),,F$8*12-1)),

I think the formula you posted is a little off in it's commas???

 

[Ron McCormick]

Jim,

Thanks for your help. I thougt the INDIRECT function might help, though
it's one I'm not very familiar with, but how do I get absolute and relative
cell references to work within the formula when I copy it across rows and
down columns?

TIA
Ron