Altering a referenced cell's formula

[Alex]

I am working on a population pyramid template for age,
sex, and race. I use simple linking between worksheets to
get the raw data into a usable form for the first race,
but then I need to systematically change the row on the
raw data listing to reflect a new race. I am wondering if
there is a way to create a formula that takes the
referenced formula in a different location and alters it
to reflect a consistent pattern.

For example:
A B C
4 White County
5 Male Beaverhead Big Horn
6 0-4 =ARSPop2000!G3 =ARSPop2000!G4
7 5-9 =SUM(ARSPop2000!H3:I3) =SUM(ARSPop2000!H4:I4)
8 ...
.....
44 Black County
45 Male Beaverhead Big Horn
46 0-4 =ARSPop2000!G59 =ARSPop2000!G60
47 5-9 =SUM(ARSPop2000!H59:I59) =SUM(ARSPop200...
48 ...
.....

I want to something like for B46 =alter(B6,G3+56)
(Alter the formula in B6 by adding 56 to G3 to make G59)
Is my idea a possibility in some worksheet function?
If not, any ideas for another way to get to the solution
would be appreciated.

Note: I need to do this repetitively. Therefore, the
find/replace function is not a usable option.

Thanks.
Alex

 

[Max]

Think using INDIRECT might do it for you ..

Try these formulas below which are equivalent to
the ones indicated in your sample data in B6:C7 and B46:C47
but with the ease to copy across after fixing it up in the starting cells

In B6: =INDIRECT("ARSPop2000!G"&COLUMN(C1))
In B7: =SUM(INDIRECT("ARSPop2000!H"&COLUMN(C1)&":I"&COLUMN(C1)))

Select B6:B7 and copy across to C7

And .. now you could just "add" 56
to the "COLUMN(C1)" parts in both B6 and B7
to get the formulas to put in B46 and B47

In B46: =INDIRECT("ARSPop2000!G"&COLUMN(C1)+56)
In B47: =SUM(INDIRECT("ARSPop2000!H"&COLUMN(C1)+56&":I"&COLUMN(C1)+56))

Select B46:B47 and copy across to C47

 

[Alex]

Recently I received assistance regarding a referencing
problem I was experiencing. (See 8/30/04 8:38am)
Another problem has surfaced. I want to take ages after 0-
4 and sum them to ten year groupings. However, I have 6
races and I need a simple method using the indirect
function to direct this procedure for all my data.

For example:
PyramidInput!
A B C
2 White County
3 Male Beaverhead Big Horn
4 0-4 253 122
5 5-9 298 156
6 10-14 337 178
......
44 Black County
45 Male Beaverhead Big Horn
46 0-4 3 2
47 5-9 1 0
48 10-14 0 1
......

PyramidInput2!
A B C
4 White County
5 Male Beaverhead Big Horn
6 0-4 =** =
7 5-14 =*** =
......
26 Black County
27 Male Beaverhead Big Horn
28 0-4 = =
29 5-14 = =
......

**In B6: =INDIRECT("PyramidInput!B"&ROW(C4))
***In B7:
=SUM(INDIRECT("APyramidInput!H"&ROW(C5)&":I"&ROW(C6)))

The first attempt at a formula B6 works, but it does not
auto-fill. The second does not work at all. Please give
me some suggestions.

 

[Max]

**In B6: =INDIRECT("PyramidInput!B"&ROW(C4))
***In B7:
=SUM(INDIRECT("APyramidInput!H"&ROW(C5)&":I"&ROW(C6)))

The first attempt at a formula B6 works, but it does not
auto-fill. The second does not work at all.

The INDIRECT suggested earlier (see extract below) uses COLUMN
as COLUMN will increment nicely as the formula is copied *across*

SInce you've changed it to ROW in B6,
this won't work when you copy *across*
(ROW is used as an incrementer for copying *down*)

As for the 2nd formula in B7, think there's a typo in your sheet ref
(an additional "A" is appended in: "APyramidInput!H" ??)
If you remove the "A", think you'd get it to work in B7,
but again since you've changed ROW for COLUMN
the formula in B7 won't fill *across*

Extract of earlier suggestion: