Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Replacing Sheet name in formula
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Replacing Sheet name in formula
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??? -- HTH... Jim Thomlinson "Ron McCormick" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Replacing Sheet name in formula
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 "Jim Thomlinson" wrote: 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??? -- HTH... Jim Thomlinson "Ron McCormick" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Replacing sheet causes #REF | Excel Discussion (Misc queries) | |||
replacing Sheet | Excel Discussion (Misc queries) | |||
Replacing sheet reference in multiple cells... | Excel Worksheet Functions | |||
replacing just the sheet names within formulas | Excel Worksheet Functions | |||
Replacing One Sheet in Several Files | Excel Programming |