N
nms
I have been trying to figure this out all day. The scenario is a follows:
1. I have a report that is created in MS Access reading from a SQL database
that I link to from a server as read only (these reports are done via a date
range of Beginning Date and Ending using a parameter query)
2. I export the report via a macro in MS Access to Excel with the date
ranges with no problems
3. Once in Excel I need to create a chart but cannot not do it in the
exported report from MS Access since the Excel spreadsheet will be
overwritten each time it is updated so I created another spreadsheet and
linked to it via the original report
4. Everything works great but each month the number of rows changes (either
more one month or less one month than the previous month) and then the
reports have to be changed manually for the source data in the chart to be
accurate. What happens is the number of rows changes: one month there are
24, the next month 54, the next 41, etc. I need a chart that will
automatically update either adding or deleting the rows as needed and I will
never have column insertions since the data is coming from a crosstab report
and query in MS Access)
5. OFFSET Function:
a. I have tried to OFFSET in various ways and it does not work when the
number of rows changes
(this is what I have pulled out of the newgroup for Excel Charts which
indicates that things will work fine if there are no row or column insertions:
=OFFSET($B$1,0,1,1,14)
which means (using the arguments left to right) define the range which
relative to $B$1, starts zero rows down and one column right, is one row
high and 14 columns wide. As long as $B$1 isn't changed by row or column
insertions, you're cool.
b. Also I have tried something similar to this and it returned all the data
links that I had added from the original spreadsheet (up to row 100) with 0s
since there was no data and I have tried this for the null values
=IF(H3=0,NA(),H3) but I still have the NA or 0s or blank spaces showing up on
the chart depening on which data I delete to try to make it work) :
=OFFSET(Sheet1!$B$1,1,0,COUNTA(Sheet1!$B:$B)-1,1)
Any help would be greatly appreciated as this is the only thing left to
complete the project!!! Thanks so much...
1. I have a report that is created in MS Access reading from a SQL database
that I link to from a server as read only (these reports are done via a date
range of Beginning Date and Ending using a parameter query)
2. I export the report via a macro in MS Access to Excel with the date
ranges with no problems
3. Once in Excel I need to create a chart but cannot not do it in the
exported report from MS Access since the Excel spreadsheet will be
overwritten each time it is updated so I created another spreadsheet and
linked to it via the original report
4. Everything works great but each month the number of rows changes (either
more one month or less one month than the previous month) and then the
reports have to be changed manually for the source data in the chart to be
accurate. What happens is the number of rows changes: one month there are
24, the next month 54, the next 41, etc. I need a chart that will
automatically update either adding or deleting the rows as needed and I will
never have column insertions since the data is coming from a crosstab report
and query in MS Access)
5. OFFSET Function:
a. I have tried to OFFSET in various ways and it does not work when the
number of rows changes
(this is what I have pulled out of the newgroup for Excel Charts which
indicates that things will work fine if there are no row or column insertions:
=OFFSET($B$1,0,1,1,14)
which means (using the arguments left to right) define the range which
relative to $B$1, starts zero rows down and one column right, is one row
high and 14 columns wide. As long as $B$1 isn't changed by row or column
insertions, you're cool.
b. Also I have tried something similar to this and it returned all the data
links that I had added from the original spreadsheet (up to row 100) with 0s
since there was no data and I have tried this for the null values
=IF(H3=0,NA(),H3) but I still have the NA or 0s or blank spaces showing up on
the chart depening on which data I delete to try to make it work) :
=OFFSET(Sheet1!$B$1,1,0,COUNTA(Sheet1!$B:$B)-1,1)
Any help would be greatly appreciated as this is the only thing left to
complete the project!!! Thanks so much...