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