Indirect function on rows

[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.
(sorry I posted this again in the main group, but I
thought I would get more attention this way.)

 

[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:

 

[Alex]

Thanks, Max for fixing my first problem!

I want to take the results from your first suggestion back
in first question I asked, and combine all age groups into
10 age intervals, except for 0-4. I am also interested in
taking the all years above 75 and adding them together.

For example:
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 = =
.......

** The cell from PyramidInput 0-4
In B6: =INDIRECT("PyramidInput!B"&COLUMN(C4))
***The summed cells from PyramidInput 5-9, 10-14
In B7:
=SUM(INDIRECT("PyramidInput!H"&COLUMN(C5)&":I"&ROW(C6)))
I used row instead of column because I want to sum rows
and not columns. Any more assistance would be VERY
appreciated.
Alex

 

[Max]

Perhaps some clarifications first to help us
understand better what's happening
when we use COLUMN and ROW

Put in any cell, say B6: =COLUMN(C4)
simply returns the col # of the cell C4, i.e. "3"
(the row# plays no part in the formula)

So for e.g.: =COLUMN(C5) also returns "3"

And when we copy B6 *across* to C6, we get the
formula in C6: =COLUMN(D4) which returns "4"

This enables COLUMN to be used
as an incrementer for copying across

Likewise for ROW

Put in any cell, say B7: =ROW(C6)
simply returns the row # of the cell C6, i.e. "6"
(the col# plays no part in the formula)

So for e.g.: =ROW(D6) also returns "6"

And when we copy B7 *down* to B8, we get the
formula in B8: =ROW(C7) which returns "7"

This enables ROW to be used
as an incrementer for copying down

COLUMN and ROW functions are used here
to return the desired numbers into the INDIRECT()

In B6: =INDIRECT("PyramidInput!B"&COLUMN(C4))

The above formula in B6 is functionally equivalent to
us putting in B6: =PyramidInput!B3

but gives the flexibility of allowing us to copy B6 across
to return the functional equivalents of putting:

in C6: =PyramidInput!B4
in D6:=PyramidInput!B5
and so on

In B7:
=SUM(INDIRECT("PyramidInput!H"&COLUMN(C5)&":I"&ROW(C6)))

The above formula in B7 is functionally equivalent to
us putting in B7: =SUM(PyramidInput!H3:I6)

But from your orig. post, I gather you actually want
In B7: =SUM(PyramidInput!H3:I3)

If that's the case, then you could put in B7:
=SUM(INDIRECT("PyramidInput!H"&COLUMN(C5)&":I"&COLUMN(C5)))
(C5 is arbitrary, you could have also used C1, C2, C3 ...)

I used row instead of column because I want to sum rows
and not columns

Trust the explanation above clarifies better
the part on the use of COLUMN and ROW
in the SUM(INDIRECT(...)).

It's got nothing to do with summing of rows / columns