Problem with Dynamic named range and Offset

[olympiac]

Hi,
I need a cell in worksheet(1) that lists the number of items in the range C4:C50 located in worksheet(Pivot Data Source)

I am using the following formula in 2007 but there must be something wrong because it doesn't work.

=OFFSET(Pivot Data Source!$C$4,4,0,COUNTA(Pivot Data Source!$C4:$C50),1)

ps: in addition, I would like to see only the items and not the empty cells in my list.
Can you assist please?

Thanks

 

[TonyMJ]

Hi

Are you able to give us an idea of what the data in the range looks like?

Presumably in your formula Pivot Data Source is incased in single quotation marks 'Pivot Data Source' otherwise you would get an error?

The second parameter in the OFFSET function is number of rows. You 4 currently which will mean your range will start a C4 + 4 rows = C8. I think you probably want this to be zero?

COUNTA counts the number of none blank cells in a range. So this works only if your data is continuous and the blanks will appear at the end which from your ps. doesn't seem to be the case?

 

[Aladin Akyurek]

Hi,
I need a cell in worksheet(1) that lists the number of items in the range C4:C50 located in worksheet(Pivot Data Source)

I am using the following formula in 2007 but there must be something wrong because it doesn't work.

=OFFSET(Pivot Data Source!$C$4,4,0,COUNTA(Pivot Data Source!$C4:$C50),1)

ps: in addition, I would like to see only the items and not the empty cells in my list.
Can you assist please?

Thanks

What kind of data do you have in C4:C50 - text or numbers?

 

[olympiac]

<TABLE style="WIDTH: 74pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=99 border=0><COLGROUP><COL style="WIDTH: 74pt; mso-width-source: userset; mso-width-alt: 3620" width=99><TBODY><TR style="HEIGHT: 18pt" height=24><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 74pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 18pt; BACKGROUND-COLOR: silver" align=left width=99 height=24>(All)</TD></TR><TR style="HEIGHT: 18pt" height=24><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 18pt; BACKGROUND-COLOR: white" align=left height=24>December-2010</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" align=left height=17>January-2011</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" align=left height=17>February-2011</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" align=left height=17>March-2011</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" align=left height=17>April-2011</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" align=left height=17>May-2011</TD></TR></TBODY></TABLE>

Where December-2010 in cell C5 is the result of the formula "=IF(B5=$B$2,"",A5)"

 

[Aladin Akyurek]

<TABLE style="WIDTH: 74pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=99><COLGROUP><COL style="WIDTH: 74pt; mso-width-source: userset; mso-width-alt: 3620" width=99><TBODY><TR style="HEIGHT: 18pt" height=24><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: silver; WIDTH: 74pt; HEIGHT: 18pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=24 width=99 align=left>(All)</TD></TR><TR style="HEIGHT: 18pt" height=24><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; HEIGHT: 18pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 height=24 align=left>December-2010</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 height=17 align=left>January-2011</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 height=17 align=left>February-2011</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 height=17 align=left>March-2011</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 height=17 align=left>April-2011</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 height=17 align=left>May-2011</TD></TR></TBODY></TABLE>

Where December-2010 in cell C5 is the result of the formula "=IF(B5=$B$2,"",A5)"

Is the data are like this:

date
date
blank
date

or like this

date
date
date
blank
blank

 

[olympiac]

I am following step by step the procedure to create dynamic named range from http://www.contextures.com/xlnames01.html


I have created in Sheet1 the following list
Cell A1: (All)
Cell A2: Orange
Cell A3: Banana
Cell A4: empty cell
etc

The following formula doesn't resize the list. Why?

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)

 

[olympiac]

in answer to your above query,

my data is

date
date
date
blank
blank

 

[Aladin Akyurek]

in answer to your above query,

my data is

date
date
date
blank
blank

And you want to exclude the trailing blanks from the definition, right?

Another important question: Are the dates true dates, not text?

 

[olympiac]

Exactly I absolutely need to exclude the blanks from the list.
The Dates are text.

 

[Aladin Akyurek]

Exactly I absolutely need to exclude the blanks from the list.
The Dates are text.

Try...

=OFFSET('Pivot Data Source'!$C$4,0,0,MATCH("*",'Pivot Data Source'!$C:$C,-1)-SUM(ROW('Pivot Data Source'!$C$4))+1)