| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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! |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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! |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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! |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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" |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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" |
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Allow EXCEL INDIRECT(ADDRESS()) and INDIRECT(RANGE()) functions | Excel Worksheet Functions | |||
| INDEX("a"&COLUMNS(INDIRECT(A1):INDIRECT(A2)):A30,4) | Excel Discussion (Misc queries) | |||
| INDIRECT.EXT | Excel Discussion (Misc queries) | |||
| INDIRECT.EXT | Excel Worksheet Functions | |||
| indirect | Excel Discussion (Misc queries) | |||
![rhhince[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
