Using INDIRECT
 

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!

Using INDIRECT
 

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" wrote in message
...
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!

Using INDIRECT
 

On Jan 9, 8:26*am, "Bob Phillips" wrote:
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.

Using INDIRECT
 

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)



"Bob Phillips" wrote in message
...
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" wrote in message
...
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!

Using INDIRECT
 

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" wrote in message
...
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!

Using INDIRECT
 

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.

Using INDIRECT
 

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" wrote in message
...
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.

Using INDIRECT
 

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.

Using INDIRECT
 

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

Tyro

"rhhince" wrote in message
...
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.

Using INDIRECT
 

On Jan 9, 12:23*pm, "Tyro" wrote:
=COUNT(INDIRECT("M9:N"&A3))/$K$8

Tyro

"rhhince" wrote in message

...



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.- Hide quoted text -

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

Using INDIRECT
 

=COUNT(OFFSET(M9,0,0,$A$3-8,2))/$K$8
--
David Biddulph
"rhhince" wrote in message
...

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"

On Jan 9, 12:23 pm, "Tyro" wrote:
=COUNT(INDIRECT("M9:N"&A3))/$K$8

"rhhince" wrote in message
...
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

Using INDIRECT
 

Use a named range

Tyro

"rhhince" wrote in message
...
On Jan 9, 12:23 pm, "Tyro" wrote:
=COUNT(INDIRECT("M9:N"&A3))/$K$8

Tyro

"rhhince" wrote in message

...



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.- Hide quoted text -

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

Using INDIRECT
 

On Jan 9, 1:13*pm, "Tyro" wrote:
Use a named range

Tyro

"rhhince" wrote in message

...
On Jan 9, 12:23 pm, "Tyro" wrote:





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

Tyro

"rhhince" wrote in message

...

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.- Hide quoted text -

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

Using INDIRECT
 

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
"rhhince" wrote in message
...
On Jan 9, 1:13 pm, "Tyro" wrote:
Use a named range

Tyro

"rhhince" wrote in message

...
On Jan 9, 12:23 pm, "Tyro" wrote:





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

Tyro

"rhhince" wrote in message

...

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.- Hide quoted text -

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

Using INDIRECT
 

Will do. Thanks.


On Jan 9, 7:02 pm, "Tyro" wrote:
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"rhhince" wrote in message

...
On Jan 9, 1:13 pm, "Tyro" wrote:



Use a named range

Tyro

"rhhince" wrote in message

...
On Jan 9, 12:23 pm, "Tyro" wrote:

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

Tyro

"rhhince" wrote in message

...

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.- Hide quoted text -

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