Dashboard Layout - column widths

[Peter]

Hi Everyone,


This is my first post here having followed the site for several years. I have learned a great deal and appreciate the friendly atmosphere.


I have looked but probably missed it and, if so, please accept my apologies. I am up against the common issue of column widths (and row heights) on a dashboard – I wonder if anyone can help me?


The major part of my planned dashboard consists of 2 tables arranged one above the other. I have used the Advanced Filter to select the particular data for presentation on the board. As you might guess each table requires different column widths. I have tried to use the camera tool but then I cannot add a scroll bar (which I will need) to the table as Robert describes here: http://chandoo.org/wp/2008/08/20/create-kpi-dashboards-excel-1/ I have also tried using very small cells and then merging them as required to obtain an efficient use of real estate, as described by Mike Alexander here: http://datapigtechnologies.com/blog/index.php/excel-report-layout-trick/ (this is a clear example of the issue I have), but this brings with it all the well-known problems of merged cells.


Are there any other techniques that I have missed?


I have had one thought but I have no idea if it is sensible or even feasible. I was wondering if it might be possible to “plot” the table onto a chart which could then have a scroll bar and being in its own frame could be positioned anywhere on the screen. Have I just embarrassed myself with a stupid idea?


Any thoughts, pointers or suggestions would be much appreciated. Be gentle!


Thanks in advance. Peter

 

[Hui]

Peter

Why can't you add a scroll bar for use with the Camera Tool?


The camera tool takes a picture of another area in the workbook

The scroll bar will allow the contents of that area to change based on some criteria

The camera tool returns the image of that area to the area of your dashboard

It is the contents of the area that the scroll bar will change not the location of the camera tool

 

[Peter]

Hui,


Thanks for your response. Sorry for the delay I have been trying what you suggest.


I have had a play with your suggestion but because the tables are so large I will need to scroll horizontally and vertically on the dashboard while maintaining visibility of column headers and row labels – a bit like “Freeze Panes”. While this is possible with the camera tool I think it entails 3 snapshots: one for the headers, one for the row labels and one for the data area. Keeping these in sync and aligned correctly on the dashboard is not an elegant solution in comparison with Robert’s use of Offset() functions. My solution involves quite a bit of vba.


Have I misunderstood what you meant?


Having said all this it seems I have not missed an obvious solution and will have to fight with one of the options in the original post.


Thanks again. Peter