| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vary the Range in a Function
I use this function
=COUNTIF(Sheet2!C$324:C$344;Sheet3!$D5) I am looking for a good way to vary the range C$324:C$344 based on a number I input in anther cell. Lets say in a1 I have the value 20. I would like the range to be C$324:C$344. If I change a1 to 50. I would like the range to be C$294:C$344. Basicaly take the end of the range - C$344 - and subtract the number in a1. Is this possible. Thank you in advance. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Vary the Range in a Function
One way:
=COUNTIF(OFFSET(SHEET2!$C$324,0,0,A1+1;1);Sheet3!$ D5) In article , carl wrote: I use this function =COUNTIF(Sheet2!C$324:C$344;Sheet3!$D5) I am looking for a good way to vary the range C$324:C$344 based on a number I input in anther cell. Lets say in a1 I have the value 20. I would like the range to be C$324:C$344. If I change a1 to 50. I would like the range to be C$294:C$344. Basicaly take the end of the range - C$344 - and subtract the number in a1. Is this possible. Thank you in advance. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Vary the Range in a Function
Maybe another way:
=COUNTIF(INDIRECT("Sheet2!" & H1 & ":" & I1),Sheet3!$D5) H1 and I1 contains the desired range (ex. C$324 and C$344) HTH, Paul "carl" wrote in message ... I use this function =COUNTIF(Sheet2!C$324:C$344;Sheet3!$D5) I am looking for a good way to vary the range C$324:C$344 based on a number I input in anther cell. Lets say in a1 I have the value 20. I would like the range to be C$324:C$344. If I change a1 to 50. I would like the range to be C$294:C$344. Basicaly take the end of the range - C$344 - and subtract the number in a1. Is this possible. Thank you in advance. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Vary the Range in a Function
Another way ... non-volatile:
Enter start row in A1, And end row in A2. =COUNTIF(INDEX(Sheet2!C:C,A1):INDEX(Sheet2!C:C,A2) ,Sheet3!$D5) -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "carl" wrote in message ... I use this function =COUNTIF(Sheet2!C$324:C$344;Sheet3!$D5) I am looking for a good way to vary the range C$324:C$344 based on a number I input in anther cell. Lets say in a1 I have the value 20. I would like the range to be C$324:C$344. If I change a1 to 50. I would like the range to be C$294:C$344. Basicaly take the end of the range - C$344 - and subtract the number in a1. Is this possible. Thank you in advance. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I create a macro that will select a range that can vary in | Excel Discussion (Misc queries) | |||
| In excel, how do I vary a cell over a data range & see its effect? | Charts and Charting in Excel | |||
| INDIRECT / Setting to vary a range? | Excel Discussion (Misc queries) | |||
| Vary bar colors by bar | Charts and Charting in Excel | |||
| How can I vary graph color by range value | Charts and Charting in Excel | |||
Hybrid Mode
