indirect with address list

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Clif McIrvin - 11 Mar 2008 20:17 GMT

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

Reply to this Message

Gary''s Student - 11 Mar 2008 20:42 GMT

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.

Signature

Gary''s Student - gsnu2007f

> I tried searching, but didn't find my solution so:
>
[quoted text clipped - 22 lines]
>
> --Clif McIrvin

Reply to this Message

Clif McIrvin - 11 Mar 2008 21:01 GMT

On Mar 11, 2:42 pm, Gary''s Student
<GarysStud...@discussions.microsoft.com> wrote:

> Here is a neater trick than INDIRECT()!
>
[quoted text clipped - 6 lines]
> --
> Gary''s Student - gsnu2007f

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

Reply to this Message

Clif McIrvin - 11 Mar 2008 21:08 GMT

> On Mar 11, 2:42 pm, Gary''s Student
>
[quoted text clipped - 26 lines]
> 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

Reply to this Message

Gary''s Student - 11 Mar 2008 21:17 GMT

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.

Signature

Gary''s Student - gsnu200773

> On Mar 11, 2:42 pm, Gary''s Student
> <GarysStud...@discussions.microsoft.com> wrote:
[quoted text clipped - 25 lines]
> M44: =I44
> M45: M42:M44

Reply to this Message

Clif McIrvin - 11 Mar 2008 21:41 GMT

On Mar 11, 3:17 pm, Gary''s Student
<GarysStud...@discussions.microsoft.com> wrote:

> 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!

--
Clif

Reply to this Message