Cell range expand or contract

  • Thread starter Thread starter Simon Jester
  • Start date Start date
S

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
 
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
 
Back
Top