More on "Data Range" - Dynamic Range Names


Bob Barnes

This is wonderful for using "OFFSET" for a Dynamic Range Name
for a Chart "Data Range"...
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:D ---> 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

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
"Refers to:" ---> =OFFSET(dyn_ranges!$A$2,0,0,COUNTA(dyn_ranges!$A$A)-1) -->
RN = week



Bob Barnes

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"...


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

Bob Barnes

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question