Dynamic References

  • Thread starter Thread starter Nigel Graham
  • Start date Start date
N

Nigel Graham

I have a workbook which is intended as a managment tool to
examin performance over the past 3 years.
I have created 36 sheets with dates running April 01 to
March 04. Data from April 01 to September 03 is populated
and the remaining are empty until needed.
What I need to be able to do is to create a formula in a
cell to show data say for April 01 and using a dynamic
link just drag over to March 04 without altering the
reference for each sheet and each month as I go along.

There are 28 measures for each month and 32 sites so the
problem is large (32,000 formulas). I have heard of
dynamic links put can find no reference in help.
 
Nigel Graham said:
I have a workbook which is intended as a managment tool to
examin performance over the past 3 years.
I have created 36 sheets with dates running April 01 to
March 04. Data from April 01 to September 03 is populated
and the remaining are empty until needed.
What I need to be able to do is to create a formula in a
cell to show data say for April 01 and using a dynamic
link just drag over to March 04 without altering the
reference for each sheet and each month as I go along.

There are 28 measures for each month and 32 sites so the
problem is large (32,000 formulas). I have heard of
dynamic links put can find no reference in help.
Click to expand...

I think you are probably thinking of dynamic range names. As a very simple
example, suppose you have the numbers 1, 2, 3, and 4 in A1:A4. You could
name the range A1:A4 as "list" and then put the formula =SUM(list) in B1,
which will obviously give the result 10. This is a normal, or fixed, range
name.

But suppose you now put the number 5 in A5, and want the named range to
extend automatically to include the newly used cell A5, so that =SUM(list)
would automatically give the result 15 just because you have entered data in
A5. This requires a dynamic range name. Instead of defining "list" as A1:A4,
you would define it by an equation that calculates a range, such as
=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)

To define a dynamic range name, you must use
Insert > Name > Define
and type the equation into the "Refers to:" box. You cannot use the "Name
Box" to define a dynamic range name, and the name will not appear in the
"Name Box". Dynamic range names are, however, very powerful; it's well worth
experimenting to get used to them. In your situation, the formula(s)
defining the name(s) could either look at how many months worth of data had
been entered, or you could have a single cell in which you define the end
month for your calculations.
 
I think I didn't explain very well.
I have sheets named 4-1 (April 2001) all the way to 3-4
(March 2004). I then gather data from the sheets to
populate a management datasheet which displays Financial
year performance i.e.
Apr | May | Jun | etc...
Sales 03-04 38 86 65 etc
02-03 76 98 76 etc
01-02 74 92 54 etc
-------------------------------------
Return 03-04 0 12 2 etc
02-03 7 31 26 etc
01-02 7 29 29 etc

Etc.. Etc....

There are 32 sites and need to display information from
each cheet on each site in the same format.
I need to reference in say Apr 03 formula like
=SUMIF(4-3!$C$1:$C$38,site!$A$1,4-3!$D$1:$D$38)
4-3 being the data sheet for April 2003 and Site being the
site being examined at the time.
The difficulty is somehowe making the 4-3! pick up 5-3!
for May 2003 and 6-3! for June etc.
Now using a text reference to the sheet names change by
setting up the first calculation then dragging across
thereby the sheet reference is picked up from cell
reference and not from the sheet.
In total there will be in excess of 32,000 calculations so
manually coding them all is a mamoth task.
I hope this is clearer.
 
Is this in the too difficult box or can it not be done?
Oh well 3 days of hard coding comming up.
 
Back
Top