OFFSET problems in dynamic range

  • Thread starter Thread starter cabybake
  • Start date Start date
C

cabybake

Hi,
I'm having the strangest problem with the OFFSET function.

I have 5 dynamic ranges in my worksheet.

Aimline
=OFFSET('Weekly ORF'!$F$4,1,0,COUNTA('Weekly ORF'$F:$F)-1,1)
Sessions
=OFFSET ('Weekly ORF'!$D$4,1,0,COUNTA('Weekly ORF'$E:$E)-1,1)
WCPM
=OFFSET ('Weekly ORF'!$G$4,1,0,COUNTA('Weekly ORF'$G:$G)-1,1)
Date
=OFFSET ('Weekly ORF'!Aimline,0,-1)
Date2
=OFFSET ('Weekly ORF'!WCPM,0,-1)

The problem is that the two names for Date and Date2 keep reverting to
=OFFSET ('Excel Template.xls'!Aimline,0,-1) and
=OFFSET ('Excel Template.xls'!WCPM,0,-1)
Which consequently prevents me from being able to adjust the SERIES in
the associated chart.

Does anyone know why this is doing this and how I can stop it? Thanks,
cabybake
 
cabybake wrote...
....
I have 5 dynamic ranges in my worksheet.

Aimline
=OFFSET('Weekly ORF'!$F$4,1,0,COUNTA('Weekly ORF'$F:$F)-1,1)
Sessions
=OFFSET ('Weekly ORF'!$D$4,1,0,COUNTA('Weekly ORF'$E:$E)-1,1)
WCPM
=OFFSET ('Weekly ORF'!$G$4,1,0,COUNTA('Weekly ORF'$G:$G)-1,1)
Click to expand...

You're missing ! between 'Weekly ORF' and the range/columns addresses
in the COUNTA calls, which DOES throw syntax errors, so these can't be
the actual formulas you're using. Are these only missing the !s?
Date
=OFFSET ('Weekly ORF'!Aimline,0,-1)
Date2
=OFFSET ('Weekly ORF'!WCPM,0,-1)
Click to expand...

Are Aimline and WCPM defined as worksheet-level names rather than
workbook-level names? If not, that's your problem.
 
Are these only missing the !s?

Oops, I actually mistyped it for this email, but it does have the !s in
the formula itself.


Are Aimline and WCPM defined as worksheet-level names rather than
workbook-level names?

How do I define these as worksheet-level vs. workbook level?

Thank you, cabybake
 
cabybake wrote...
....
Are Aimline and WCPM defined as worksheet-level names rather than
workbook-level names?

How do I define these as worksheet-level vs. workbook level?
Click to expand...

Names may be defined at workbook-level (default) or worksheet-level. If
you highlight the range B2:E5 in the worksheet named foo, then run
Insert > Name > Define and enter just

bar

as the name, then bar will be a workbook-level name. On the other hand,
if you enter the name as

foo!bar

then bar will be a worksheet-level name which you may refer to just as
bar in the foo worksheet, but as foo!bar in other worksheets.

If you run Insert > Name > Define, do the lines containing Aimline or
WCPM in the name list show the worksheet names on the right hand side
of the line? If not, they're workbook-level names. If they're
workbook-level names, then you shouldn't precede then with worksheet
names.

So, what happens when you change the definitions of Date and Date2 to

Date
=OFFSET (Aimline,0,-1)

Date2
=OFFSET (WCPM,0,-1)

?
 
Back
Top