Referencing Sheet Names In Formula

[Paul Gurdin]

I have a number of sheets in a workbook named

Region 1 Actuals
Region 2 Actuals
Region 3 Actuals and so on (26 in total)

I have a summary sheet within which I am using the offset
function to refer to the Regional Actuals sheet.

What I am trying to do is to type into one cell on the
summary sheet the Region number which then automatically
updates all formulae within the cells on the summary sheet.

eg

I can type the formula =offset('Region 1 Actuals'!G3,2,0)
and this returns the right result.

However what I want to create is a formula that
automatically changes the 'Region 1 Actuals' part of the
formula, if the content of cell A1 in the summary sheet is
change to (say) Region 2.

I don't really want to go down the route of writing a
custom formula and some code to carry out a replace all
action if I can help it, so if somebody knows a built in
formula that can do this for me I would be eternally
grateful (so will my boss as it's because of him I am not
allowed to go down the custom formula route!!)

Many TIA

 

[J.E. McGimpsey]

one way:


=OFFSET(INDIRECT("'" & A1 & " Actuals'!G3"), 2, 0)

 

[paul gurdin]

Many thanks for help. The function works fine.>-----
Original Message-----

 

[David McRitchie]

Hi Paul,
I have the impression that you want to copy formulas down and across
using the fill handle, and if that is the case I can't tell from
a single formula what you want to the right and down for formulas.

You mention putting on value into A1, so I imagine that your
reference to A1 will be referred to by all formulas on the sheet
and you would want to use $A$1 so that using the fill handle
to replicate formulas will not change it's address.