I have a problem,
I am using Excel 2007. In my worksheet, I have table/grid with 7cols X 200rows
Column headers are:
1. site
2. modality
3. status
4. staff
5. total ytd
6. total mtd
7. total wtd
Please refer to attachment. (NB chart is not shown in workbook). Issue is, the way the grid is populated is such that only the rows corresponding to user selected parameters are populated and these in turn populate a chart.
for example,
A user may choose a site, modality, status and timeslice (ytd or mtd etc). It is not possible for a user to view more than 1 site, modality, status or timeslice at any given time because input of parameters is mandatory, but departments can be multiple. One important point to make is that, in the displayed dataset, site, modality, status are duplicated.
Problem is, the position of the dataset moves up and down the table based on the selected values. this results in the position of the bars on the chart being squeezed to one corner especially when the dataset is close to the end of the grid, making the chart unreadable.
I am looking for a way to create an intermediatary grid/table on the spreadsheet that has about 20 rows and the 7columns (20 rows because for any given set of parameters, the dataset will not exceed 20 rows) but the values in this grid would change based on parameters - (some sort of lookup). I plan to populate the chart with this grid.
I will be very grateful is anyone can help with this.
Bookmarks