Multiple charts from the same data table (filtered)

[BananaApple]

Hello everyone,


Thank you for this wonderful site and forum. I am learning a lot, and I can't believe this site exists!


What I want to do should be very simple but I cannot figure it out.


I have some data in a very large table, which I update each month.


Each month, I want to print out multiple charts IN THE SAME PAGE, comparing the data. For example, data from Room A, Room B, and Room C.


Or data for Operator A, Operator B, and Operator C.


I am guessing I could do it by filtering the table. But then I only know how to filter (manually) for one parameter at a time, so I can only print one parameter's chart at a time. However, I need all the different charts to appear (automatically) and be printed side-by-side.


Also, I want to plot raw data, not the sum, mean, max etc, otherwise I think I could use Pivot Tables.


Can anyone help?


Please see some example data here:


https://docs.google.com/file/d/0B2C1shgLSBb5amVCaFZUM3hUYk0/edit?usp=sharing

 

[NARAYANK991]

Hi ,


I am not sure I have understood you ; can you please clarify ?


You say your table is very large ; you say you want to print multiple charts in the same page ; can you say how many charts will be placed on one page ?


You say you will want data from several rooms on the same page ; can you say how many rooms are there in total , and how will you want to group the rooms on any page i.e. if you have 7 rooms , will you want to have rooms A , B and C on the first page , D , E and F on the second , and G on the third ?


The same information is required for the other kinds of charts e.g. you have mentioned grouping charts by operators.


Narayan

 

[Hui]

BananaApple


It is typical to have:

1. a Data Table

2. Then to extract various reports from the Data Table

3. Then chart/summarise this extracted data


You need simply add the last two points to your model

You will need to extract the data in a suitable format to chart

I'd suggest having a read of Lukes post at:

http://chandoo.org/wp/2011/11/18/formula-forensics-003/


You will need to setup several extract areas/worksheets to suit your needs

 

[BananaApple]

Narayank991,


Thanks for your reply. The table will be almost exactly like the spreadsheet, but with many, many more rows.


I would like to group 8 rooms (for example) in one page. There may be up to 16 rooms.


Hui,

Thanks for your reply too! I am checking out Luke's example. It is great, and what I need. However, at first glance, because the formulas are relatively complex (to me!), it seems to be quite easy for an inexperienced user to accidentally change the final output. The data will be input by operators who are not familiar with Excel. Therefore if something goes wrong, it is difficult for them to correct the output.


Is there any way to simplify or error-proof it?


I am thinking I need to put the extracted data on a different sheet, for starters.

 

[NARAYANK991]

Hi ,


I am still not sure what you want ; is your requirement formulae to extract data , or is your requirement the printing of multiple charts on one or more pages ?


If it is the latter , you have mentioned that you would like to see data for 8 rooms on one page , which means 8 charts at a time ; it is clear that 8 different charts cannot share the same data ; you will need 8 separate data ranges ( either physically separated or separated using formulae ) to cater to these 8 charts.


The next point is that if these 8 appear on one page , how do we get the next 8 , which have to be printed on the second page.


I think it will take some time to work on this , and come up with a convenient solution.


Narayan