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

one way:


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


In article ,
"Paul Gurdin" wrote:

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

Many thanks for help. The function works fine.-----
Original Message-----
one way:


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


In article ,
"Paul Gurdin" wrote:

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
.

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.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"J.E. McGimpsey" wrote in message ...
one way:


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


In article ,
"Paul Gurdin" wrote:

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