Cell range expand or contract

[Simon Jester]

I inherited an Excel workbook that pulls a great deal of its data from
an Access db using MSQuery.
Some of the worksheets contain bar charts that get their source data
from the information pulled in from Access.
I am having trouble finding a way to make the cell ranges that hold
the chart source data expand or contract depending on the amount of
data. For example, one month the source for chart A may be Data!
A1:B5. The next month there may have been more transactions and the
range would be Data!A1:B10. Right now the user goes in and manually
adjusts the cell ranges. Is there some way to make this happen
without user intervention? Any solutions/suggestions you could pass
my way would be greatly appreciated.

Thanks very much!
SJ

 

[Jim Rech]

If you can assume that there is nothing else besides the data in the sheet
or at least in column A and row 1 you could create a dynamic name like
"DBase" with this definition to refer to the data:

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

Replace Sheet1 with the name of your sheet of course.

--
Jim
|I inherited an Excel workbook that pulls a great deal of its data from
| an Access db using MSQuery.
| Some of the worksheets contain bar charts that get their source data
| from the information pulled in from Access.
| I am having trouble finding a way to make the cell ranges that hold
| the chart source data expand or contract depending on the amount of
| data. For example, one month the source for chart A may be Data!
| A1:B5. The next month there may have been more transactions and the
| range would be Data!A1:B10. Right now the user goes in and manually
| adjusts the cell ranges. Is there some way to make this happen
| without user intervention? Any solutions/suggestions you could pass
| my way would be greatly appreciated.
|
| Thanks very much!
| SJ

 

[Gord Dibben]

See Tushar Mehta's site for a short course on Dynamic Charting and some sample
files.

http://www.tushar-mehta.com/excel/newsgroups/dynamic_charts/index.html

Alternative site is Stephen Bullen's.

http://www.oaltd.co.uk/Excel/Default.htm

Download either or both of FunChrt1 and FunChrt7


Gord Dibben MS Excel MVP