Reports Charts

[GN0001]

I receive some reports in an identical format in excel and I have to insert charts for them. Is there anyway that I can put the report in the workbook that I have the charts and get my charts prepared without having to do data entry.

Can we do a cell reference on the table of the charts.The values in the table are getting aggregated, I mean this week value is added to what I have in the past.

Regards,

Guity

 

[Luke M]

You mean something like this?

http://peltiertech.com/Excel/Charts/DynamicLast12.html

 

[GN0001]

Hi Luke,


I have three rows of data, it is always coming in three rows and 30 columns. I need to insert charts on this data.


For example:

I have Value 3 in cell A2 in sheet A in first week from the report of the first week and then next week, when I receive the report, I have to keep 3 and add the new value to it.


Any tips? any thought? I need it for my work. My boss tells me to automate these charts and not spent time on it.

Guity

 

[Luke M]

Did you read the link I posted? It shows how to setup a chart where you define how many points (weeks/months/etc) to look at, and then you can just keep adding data to the table. From your last post, it sounds like you have something like this:

(repeat for 30 columns)

Week 1: 5

Week 2: 6

Week 3: 5


and then later, you'll add data and make it this:

(repeat for 30 columns)

Week 1: 5

Week 2: 6

Week 3: 5

Week 4: 7

Week 5: 8

 

[GN0001]

Hello Luke,

I read the link, but what I have is:


I have a value in cell A3, for example number 3, then next week from the report I receive I have to add number 4 to number 3. I should add the data to existing data. I don't enter my value into a new cell. Also, I don't have labels like week 1, week 2, or week 3, I enter my values to the cell like this: (3+4+2)


3 belongs to first week, 4 belongs 3 to second week and 2 belongs to third week.


Thank you for the help, I need it for my work.

Guity

 

[Hui]

You can copy the 3 Row x 30 Column area for your new data

and then Paste Special, Add

pasting the entire area over the existing data, which will add teh values to the underlying values

 

[GN0001]

Hui,

If I copy and paste the values over the my old data in the data table, that would only paste the values from the current week. what happens to my old data?

Thanks,

Guity

 

[Hui]

If you use Paste As, Formula's Add it will add your new data to your old data

And it will show you =(3+4)+2

where 2 is the data you pasted

Next week it will be

=((3+4)+2)+x


I'd be more inclined not to do that, but to paste it below the old data and then use a summary table to extract and rearrange the data to suit your requirements

 

[GN0001]

Hui,


I understand what you say. But my boss wants me to automate the whole procedure. Is there any way? If not, then I can go ahead and say firmly that there is no way.


Why do you say that you are not willing to do so?


Do you think the idea of dynamic chart works for this situation?


Regards,

Guity

 

[Hui]

Guity

Without seeing how your data is organised or what your trying to do it is very hard to say anythinge except generalisations


I would setup an import sheet

and then using a macro transfer that data onto a storage sheet, which is appropriately arranged to allow either querying or direct charting from it


The charts can be linked to Dynamic Named ranges

 

[GN0001]

Hui & Luke,

I understand it, I could learn a lot if I was able to share the data. However my data is very close to what I explain here;


I have 4 to 6 charts on each sheet, and my table is very simple like below.

2010 2011

Category A 20 (3+2+1)

Category B 30 (4+2+0)

Category C 15 (6+2+12)

Category D 4 (3+9+0)


The charts are coming from the tables from 1 to 3 columns. I have added the quantities from each week in parenthesis to keep track of quantities that I receive from the reports each week. I want to quantity of each week to be added to what I have in parenthesis automatically, instead of me being have to enter them manually. Can excel take car of such a task? How about dynamic charts?


I greatly appreciate your help.

Guity