Hi there,
Previously I have been able to just peruse through as a guest and help myself out with my problems; however I have finally encountered a problem that I cannot fudge my way through.
Setting;
1 2 3 4
A 30% 35% 45%
B 12% 13% 45%
C 22% 32% 55%
Each week a co-worker will be adding data down columns, (a simple copy and paste), so for column 1, they would have copied and pasted all the data for A,B,C. This is then displayed on an individualised chart, charting the % that A did during week 1, then 2 etc. What I would like to happen is that when they enter the number in column 4 (being the number 4), and pasting a new weeks’ worth of data, the chart auto updates.
Attempts;
So far I have tried to have both week and letter set up as dynamically named ranges that are inserted into the chart so each week is updated as they insert data, keeping them from having to change the data source of the chart for each week. *note final version has 52 charts, hence the need for auto updating. However, using the following formula I have only been able to have weeks heading down the rows and A,B & C heading across columns. The table to input data cannot change.
Formula tested;
The formula I have tried is; =OFFSET (Sheet1! $A$2, 0, 0, COUNTA (Sheet1! $A$2: A$2) -1).
I have fiddled around with it so much that this is the base formula I was working with, at one stage I got it so that I could go into the name manager and increase the last digit by 1 and it would add the next column of data into the chart, but I needed this to be automatic.
If this is possible through the way I have tried so far, excellent, if not I am open to a solution that has a macro button above the table that can be pressed to either increase this last digit by 1 or select the next weeks’ worth of data to be included.
Thanks.
Bookmarks