Using INDIRECT

[rhhince]

I have a formula which works very well in column L

=(COUNT(INDIRECT("M9"&":N"&$A$3))/$K8)

This counts my data in column M and N.
I I insert a new column before column L, the formula doesn't change to
adjust to the shift.
I would like it to read:

=(COUNT(INDIRECT("N9"&":O"&$A$3))/$K8)

The reference to column M and N is hardwired. You would think that M
and N not having a $ in front of it, that it would do so, but it
doesn"t.

How can I get the formula to automatically adjust to the addition of
another column.

Thanks!

 

[Bob Phillips]

Try this

=COUNT(OFFSET(M$1,8,0,1000,2))/$K8

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[rhhince]

Try this

=COUNT(OFFSET(M$1,8,0,1000,2))/$K8

That doesn't work Bob. Reason being that the formula is counting a
dynamic range Column M9 to Column N(whatever). Your suggestion shifts
the formula down a row, which doesn't suit the situation. I don't wish
to change location of the formula, but simply the column reference
when adding a new column. Thanks for the input though.

 

[Bob Phillips]

Sorry, I forgot the A3 bit

=COUNT(OFFSET(M$1,8,0,$A$3,2))/$K8

Not sure what you mean by shifting down a row.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Tyro]

The reason that Excel does not change the formula is that "M9" and "N" are
text. Would you want Excel to change your text?

Tyro

 

[rhhince]

Actually M9 and N in =(COUNT(INDIRECT("M9"&":N"&$A$3))/$K8) are
referencing a range of numbers. The original formula is =COUNT(M9:N29)/
$K$8. I discovered that if I used the new formula, I don't have to
change dozens of formulas manually. This works, but not if I want to
add columns and build the worksheet.

 

[Tyro]

I know that M9 and N reference cells. But Excel sees them as text because
they are in quotes. Therefore Excel does not change them. Excel does not
change text.

Tyro

 

[rhhince]

Here is really what I would like:

The original formula is =COUNT(M9:N29)/$K$8

What modification could be used to change the number 29, referring it
to a cell value taken from A3 which is 29.
If A3 changed to the number 30. the formula would read =COUNT(M9:N30)/
$K$8.

 

[Tyro]

=COUNT(INDIRECT("M9:N"&A3))/$K$8

Tyro

 

[rhhince]

=COUNT(INDIRECT("M9:N"&A3))/$K$8

Tyro








- Show quoted text -

That works for changing the number, but when I add a column, I am
faced with the same dilemma "M9:N" doesn't change to "N9:O"

 

[David Biddulph]

=COUNT(OFFSET(M9,0,0,$A$3-8,2))/$K$8

 

[Tyro]

Use a named range

Tyro

=COUNT(INDIRECT("M9:N"&A3))/$K$8

Tyro








- Show quoted text -

That works for changing the number, but when I add a column, I am
faced with the same dilemma "M9:N" doesn't change to "N9:O"

 

[rhhince]

Use a named range

Tyro





That works for changing the number, but when I add a column, I am
faced with the same dilemma "M9:N" doesn't change to "N9:O"- Hide quoted text -

- Show quoted text -

Thanks everyone for your input. My brain is fried for the moment.
Easier to manually change for now.

Ray

 

[Tyro]

We will be here for you, fried brains and all. Do us a favor and post your
messages at the top of your post, not at the bottom.

Tyro

Use a named range

Tyro





That works for changing the number, but when I add a column, I am
faced with the same dilemma "M9:N" doesn't change to "N9:O"- Hide quoted
text -

- Show quoted text -

Thanks everyone for your input. My brain is fried for the moment.
Easier to manually change for now.

Ray

 

[rhhince]

Will do. Thanks.

We will be here for you, fried brains and all. Do us a favor and post your
messages at the top of your post, not at the bottom.







Thanks everyone for your input. My brain is fried for the moment.
Easier to manually change for now.

Ray