Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
OFFSET function in named range returning wrong # of rows
I am trying to create a dynamically named range. However, the range keeps
selecting too many cells. This function: =COUNTIF(PivotMONTH!$I:$I,"0") returns 47, which is the correct number of rows. However, this named range which contains the above function: =OFFSET(PivotMONTH!$G$4,0,0,(COUNTIF(PivotMONTH!$I :$I,"0")),5) returns a range 58 rows long. My data does indeed have 58 rows, but the last few rows are just zeroes. My COUNTIF function should eliminate those rows. Why does my countif return the correct number of rows when it stands alone, but an incorrect number when it is used in the named range? Thanks! Heidi |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
OFFSET function in named range returning wrong # of rows
What are you trying to do with OFFSET? OFFSET has the following arguments:
=OFFSET(reference,rows,cols,height,width) of which arguments the first three are required. With your formula =OFFSET(PivotMONTH!$G$4,0,0,(COUNTIF(PivotMONTH!$I :$I,"0")),5) it looks like your nested COUNTIF is the height argument of OFFSET Assuming your intentions square with the above, which column do the 0 values occur in? Dave -- A hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. "Heidi" wrote: I am trying to create a dynamically named range. However, the range keeps selecting too many cells. This function: =COUNTIF(PivotMONTH!$I:$I,"0") returns 47, which is the correct number of rows. However, this named range which contains the above function: =OFFSET(PivotMONTH!$G$4,0,0,(COUNTIF(PivotMONTH!$I :$I,"0")),5) returns a range 58 rows long. My data does indeed have 58 rows, but the last few rows are just zeroes. My COUNTIF function should eliminate those rows. Why does my countif return the correct number of rows when it stands alone, but an incorrect number when it is used in the named range? Thanks! Heidi |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
OFFSET function in named range returning wrong # of rows
Dave,
Thanks for replying! My data are in columns I, J and K. The entire range should include columns G-K and as many rows as there are lines of data, not including rows of zeroes at the end. Currently, there are only 47 rows that contain numbers in I- the remaining rows contain zeroes. They will be filled in as time goes on. I'd like to create a dynamically named range that lets me keep an updated graph going without having to reset the "source data" for the graph each time new data is added. I've done this with other ranges of data, but this one with the zeroes at the end is causing more problems... Thanks, Heidi "Dave F" wrote: What are you trying to do with OFFSET? OFFSET has the following arguments: =OFFSET(reference,rows,cols,height,width) of which arguments the first three are required. With your formula =OFFSET(PivotMONTH!$G$4,0,0,(COUNTIF(PivotMONTH!$I :$I,"0")),5) it looks like your nested COUNTIF is the height argument of OFFSET Assuming your intentions square with the above, which column do the 0 values occur in? Dave -- A hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. "Heidi" wrote: I am trying to create a dynamically named range. However, the range keeps selecting too many cells. This function: =COUNTIF(PivotMONTH!$I:$I,"0") returns 47, which is the correct number of rows. However, this named range which contains the above function: =OFFSET(PivotMONTH!$G$4,0,0,(COUNTIF(PivotMONTH!$I :$I,"0")),5) returns a range 58 rows long. My data does indeed have 58 rows, but the last few rows are just zeroes. My COUNTIF function should eliminate those rows. Why does my countif return the correct number of rows when it stands alone, but an incorrect number when it is used in the named range? Thanks! Heidi |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
OFFSET function in named range returning wrong # of rows
Heidi,
Worked OK for me on a simple test. I added /deleted rows with 0s and displayed the correct range. XL2003 "Dave F" wrote: What are you trying to do with OFFSET? OFFSET has the following arguments: =OFFSET(reference,rows,cols,height,width) of which arguments the first three are required. With your formula =OFFSET(PivotMONTH!$G$4,0,0,(COUNTIF(PivotMONTH!$I :$I,"0")),5) it looks like your nested COUNTIF is the height argument of OFFSET Assuming your intentions square with the above, which column do the 0 values occur in? Dave -- A hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. "Heidi" wrote: I am trying to create a dynamically named range. However, the range keeps selecting too many cells. This function: =COUNTIF(PivotMONTH!$I:$I,"0") returns 47, which is the correct number of rows. However, this named range which contains the above function: =OFFSET(PivotMONTH!$G$4,0,0,(COUNTIF(PivotMONTH!$I :$I,"0")),5) returns a range 58 rows long. My data does indeed have 58 rows, but the last few rows are just zeroes. My COUNTIF function should eliminate those rows. Why does my countif return the correct number of rows when it stands alone, but an incorrect number when it is used in the named range? Thanks! Heidi |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
OFFSET function in named range returning wrong # of rows
Well, I tried your formula and it works for me, and it works for Toppers as
well, so something about your data seems wrong. Are the 0s formatted as numbers or text? Dave -- A hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. "Heidi" wrote: Dave, Thanks for replying! My data are in columns I, J and K. The entire range should include columns G-K and as many rows as there are lines of data, not including rows of zeroes at the end. Currently, there are only 47 rows that contain numbers in I- the remaining rows contain zeroes. They will be filled in as time goes on. I'd like to create a dynamically named range that lets me keep an updated graph going without having to reset the "source data" for the graph each time new data is added. I've done this with other ranges of data, but this one with the zeroes at the end is causing more problems... Thanks, Heidi "Dave F" wrote: What are you trying to do with OFFSET? OFFSET has the following arguments: =OFFSET(reference,rows,cols,height,width) of which arguments the first three are required. With your formula =OFFSET(PivotMONTH!$G$4,0,0,(COUNTIF(PivotMONTH!$I :$I,"0")),5) it looks like your nested COUNTIF is the height argument of OFFSET Assuming your intentions square with the above, which column do the 0 values occur in? Dave -- A hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. "Heidi" wrote: I am trying to create a dynamically named range. However, the range keeps selecting too many cells. This function: =COUNTIF(PivotMONTH!$I:$I,"0") returns 47, which is the correct number of rows. However, this named range which contains the above function: =OFFSET(PivotMONTH!$G$4,0,0,(COUNTIF(PivotMONTH!$I :$I,"0")),5) returns a range 58 rows long. My data does indeed have 58 rows, but the last few rows are just zeroes. My COUNTIF function should eliminate those rows. Why does my countif return the correct number of rows when it stands alone, but an incorrect number when it is used in the named range? Thanks! Heidi |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
OFFSET function in named range returning wrong # of rows
I think I figured out the problem.
I tried it in a new workbook and it worked as expected, as both you and Toppers found too. I think the problem is that column G contains merged cells. They are merged to make a prettier X-axis labels in the graph that is generated, but obviously that appears to be messing up my auto-updating selection. I'll work on a different way to organize the data so that isn't a problem. Thanks for your help! Heidi "Dave F" wrote: Well, I tried your formula and it works for me, and it works for Toppers as well, so something about your data seems wrong. Are the 0s formatted as numbers or text? Dave -- A hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. "Heidi" wrote: Dave, Thanks for replying! My data are in columns I, J and K. The entire range should include columns G-K and as many rows as there are lines of data, not including rows of zeroes at the end. Currently, there are only 47 rows that contain numbers in I- the remaining rows contain zeroes. They will be filled in as time goes on. I'd like to create a dynamically named range that lets me keep an updated graph going without having to reset the "source data" for the graph each time new data is added. I've done this with other ranges of data, but this one with the zeroes at the end is causing more problems... Thanks, Heidi "Dave F" wrote: What are you trying to do with OFFSET? OFFSET has the following arguments: =OFFSET(reference,rows,cols,height,width) of which arguments the first three are required. With your formula =OFFSET(PivotMONTH!$G$4,0,0,(COUNTIF(PivotMONTH!$I :$I,"0")),5) it looks like your nested COUNTIF is the height argument of OFFSET Assuming your intentions square with the above, which column do the 0 values occur in? Dave -- A hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. "Heidi" wrote: I am trying to create a dynamically named range. However, the range keeps selecting too many cells. This function: =COUNTIF(PivotMONTH!$I:$I,"0") returns 47, which is the correct number of rows. However, this named range which contains the above function: =OFFSET(PivotMONTH!$G$4,0,0,(COUNTIF(PivotMONTH!$I :$I,"0")),5) returns a range 58 rows long. My data does indeed have 58 rows, but the last few rows are just zeroes. My COUNTIF function should eliminate those rows. Why does my countif return the correct number of rows when it stands alone, but an incorrect number when it is used in the named range? Thanks! Heidi |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
named range row offset | Excel Worksheet Functions | |||
Dynamic named range & Offset | Excel Discussion (Misc queries) | |||
Offset delivers value error with Named range | Excel Worksheet Functions | |||
named range, offset self-reference | Excel Discussion (Misc queries) | |||
Offset Function works in cell, not in named range | Excel Worksheet Functions |