Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
Name ranges and insertions to spreadsheet to update charts
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... |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dynamic charts without Named Ranges? | Charts and Charting in Excel | |||
Using Named Ranges with Charts: Part 2 (!) | Excel Worksheet Functions | |||
Excel insertions (drawings, word art, pics, etc) are not visible. | Excel Discussion (Misc queries) | |||
Limiting ranges when building charts in vba | Excel Discussion (Misc queries) | |||
2 DATA RANGES IN CHARTS | Charts and Charting in Excel |