More on "Data Range" - Dynamic Range Names

[Guest]

This is wonderful for using "OFFSET" for a Dynamic Range Name
for a Chart "Data Range"...http://processtrends.com/pg_dynamic_charts.htm
Thank you again Kelly O'Day.

It includes, for the X-Axis Dynamic Range Name...
"Refers to:" ---> =OFFSET(dyn_ranges!$A$2,0,0,COUNTA(dyn_ranges!$A$A)-1)

Currently, I'm using Column A to receive several Ranges for Charts from
Access Automation.

The...COUNTA(dyn_ranges!$A$A) will Count Entries in Column A from Cell
$A$2 down. Other than separating the Ranges...IE..
For Chart 1...Columns A:B ---> COUNTA(dyn_ranges!$A$A)
For Chart 2...Columns C ---> COUNTA(dyn_ranges!$C$C)
For Chart 1...Columns E:F ---> COUNTA(dyn_ranges!$E$E)
For Chart 1...Columns G:H ---> COUNTA(dyn_ranges!$G$G)

....Is there a way to COUNTA(dyn_ranges!$A$A) - "Down x" number of Rows??

TIA - Bob



http://processtrends.com/pg_dynamic_charts.htm

From the URL directly above
We want to setup our chart so that it automatically updates as we add
additional weeks of flow data. We do this by
creating dynamic range names that expand as we add data.
The Dynamic Expanding Range - For the X-Axis
The Define Name window appears. We add "Week" in Name box and enter an
Offset formula in the Refers to box to
define our dynamic range name. The general offset formula is:
= Offset(start cell, rows offset, cols offset, number of rows, number of
cols)
"Refers to:" ---> =OFFSET(dyn_ranges!$A$2,0,0,COUNTA(dyn_ranges!$A$A)-1) -->
RN = week

 

[Guest]

Being an Access Programmer, I don't know Excel well..
I guess this will work, where "YBooth" is the Range I want
to count in "COUNTA"...

=OFFSET(DataSheet!$A$109,0,0,COUNTA("Ybooth")-1)

I want to replace "DataSheet!$A$109" w/ another range Name...Will try
=OFFSET(DataSheet!$WBooth,0,0,COUNTA("Ybooth")-1)...and see if that works.

TIA - Bob

 

[Guest]

I want to replace "DataSheet!$A$109" w/ another range Name...Will try
=OFFSET(DataSheet!$WBooth,0,0,COUNTA("Ybooth")-1)...and see if that works

"DataSheet!$WBooth" above doesn't work...Is there a way to use a Range Name
instead of "DataSheet!$A$109" ???

TIA - Bob

 

[Jon Peltier]

Omit the dollar sign in DataSheet!WBooth. Also you may need to qualify
Ybooth with the sheet name (e.g., DataSheet!Ybooth).

- Jon