indirect with address list

[Clif McIrvin]

I tried searching, but didn't find my solution so:

I have a series of formulas that operate on a list of cells, but the
list is variable. I thought to replace all references to the cell list
with indirect, but it appears to fail on an address list.

For simplicity, I'll use only the average function as example rather
than multiple formulas:

Cell I45 contains =average(I30,I37,I44)

There is an empty sell at H46, so I tried:

H46: I27,I34,I41
I45: =average(indirect(H46))

which fails.

Is there a way to accomplish this?

The reason is (for instance) when the list is I27,I34 instead of
I27,I34,I41 I don't wish to change several formulas.

Thanks in advance!

--Clif McIrvin

 

[Gary''s Student]

Here is a neater trick than INDIRECT()!

1. Select a pile of cells (click the first one and CNTRL-click the rest)
2. Insert > Name > Define > clif
3. Then use:
=AVERAGE(clif)
You can use clif across many formulas. You only need to change the Defined
Name from the menu rather than change all the cells that use it.

 

[Clif McIrvin]

Here is a neater trick than INDIRECT()!

1. Select a pile of cells (click the first one and CNTRL-click the rest)
2. Insert > Name > Define > clif
3. Then use:
    =AVERAGE(clif)
You can use clif across many formulas.  You only need to change the Defined
Name from the menu rather than change all the cells that use it.

But, the plot thickens. I debated how much detail to include, I see I
left out a critical bit:

The workbook contains a pile of sheets of identical format, and this
situation exists on each sheet (in multiple workbooks.) this "named
range" must be able to be different on each sheet.

In the meanwhile, I have devised a work-around utilizing space "off to
the side" (that is, outside of the print view.)


In column M I have created a stack of formulas, for instance:

M42: =I30
M43: =I37
M44: =I44
M45: M42:M44

 

[Clif McIrvin]

But, the plot thickens.  I debated how much detail to include, I see I
left out a critical bit:

The workbook contains a pile of sheets of identical format, and this
situation exists on each sheet (in multiple workbooks.) this "named
range" must be able to be different on each sheet.

In the meanwhile, I have devised a work-around utilizing space "off to
the side" (that is, outside of the print view.)

In column M I have created a stack of formulas, for instance:

M42:   =I30
M43:   =I37
M44:   =I44
M45:   M42:M44

aarggh! Fat-finger error!

and now

I45: =average(indirect(M45))

works as I expect it to. When the data on a new sheet has different
requirements, I can simply change M45 to, say, M42:M43 and the
formulas behave as I expect.

(I am dealing with test data, and each sheet represents a different
sample. The more I learn about what Excel (and Access) can do
the more I believe I would have set things up differently, but - alas!
-
this is an inherited system, so for now, at least, I'm stuck with the
layout I have.)

--Clif

 

[Gary''s Student]

Your workaround is a good one. Many functions have problems handling a
disjoint collects of cells; COUNTIF() is one example.

A perfectly valid solution to to removed "disjointness" by creating a nice,
compact block of linked cells and let the fussy function operate on them
instead.

 

[Clif McIrvin]

Your workaround is a good one.  Many functions have problems handling a
disjoint collects of cells; COUNTIF() is one example.

A perfectly valid solution to to removed "disjointness" by creating a nice,
compact block of linked cells and let the fussy function operate on them
instead.

Thanks! that feels good!

I've noticed your sig under good solutions before --- Thanks for your
helpfulness!