Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 75
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,574
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 75
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 4,339
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,574
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 75
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
named range row offset [email protected] Excel Worksheet Functions 5 July 8th 06 01:35 AM
Dynamic named range & Offset fastballfreddy Excel Discussion (Misc queries) 1 May 4th 06 09:00 AM
Offset delivers value error with Named range [email protected] Excel Worksheet Functions 4 November 29th 05 01:49 PM
named range, offset self-reference George Excel Discussion (Misc queries) 6 November 7th 05 12:21 AM
Offset Function works in cell, not in named range DragonslayerApps Excel Worksheet Functions 0 July 25th 05 04:39 PM


All times are GMT +1. The time now is 03:22 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"