Hi all,
Compared to most people here, I’m a beginner, but I learned a lot recently and thought about making a first dashboard. I thought it would be easy, and it probably is, but I can’t seem to get my head around it…so here it is:
On my sample file, you see 3 similar tables. They represent the number of people living in the different districts of Brussels, Belgium (vertical) along recent years (horizontal). First table is the sum of men and women, second table is men only and third table women. Anything from row 22 (which is only a total of the previous rows) until bottom is of no concern, I don’t intend to use it.
What I would like to do is the ability to monitor different things:
1) To have at will a graph for any district, with a horizontal axis showing years and the number of people on the vertical axis. If possible, to have for each district the number of men and women side-by-side or the total.
2) To have at will a graph for any year, with a horizontal axis showing districts and the number of people on the vertical axis.
This kind of flexibility naturally leads to a pivot table, but the result is far from what I desire: I can’t put “year” on an axis, I just can put a precise year and even then, it’s a mess.
I tried to consolidate the men and women table, so I have outlining for each district. Looks ok on table, but it seems impossible to have a decent graph out of it.
I think I could make my way by re-doing a new table for every possible scenario, but there must be a more elegant way to do that.
What am I doing wrong? What would be your method? I’m pretty confident about my technical skills, including VBA, but I definitely lack some practical methodology. Thanks in advance!
Compared to most people here, I’m a beginner, but I learned a lot recently and thought about making a first dashboard. I thought it would be easy, and it probably is, but I can’t seem to get my head around it…so here it is:
On my sample file, you see 3 similar tables. They represent the number of people living in the different districts of Brussels, Belgium (vertical) along recent years (horizontal). First table is the sum of men and women, second table is men only and third table women. Anything from row 22 (which is only a total of the previous rows) until bottom is of no concern, I don’t intend to use it.
What I would like to do is the ability to monitor different things:
1) To have at will a graph for any district, with a horizontal axis showing years and the number of people on the vertical axis. If possible, to have for each district the number of men and women side-by-side or the total.
2) To have at will a graph for any year, with a horizontal axis showing districts and the number of people on the vertical axis.
This kind of flexibility naturally leads to a pivot table, but the result is far from what I desire: I can’t put “year” on an axis, I just can put a precise year and even then, it’s a mess.
I tried to consolidate the men and women table, so I have outlining for each district. Looks ok on table, but it seems impossible to have a decent graph out of it.
I think I could make my way by re-doing a new table for every possible scenario, but there must be a more elegant way to do that.
What am I doing wrong? What would be your method? I’m pretty confident about my technical skills, including VBA, but I definitely lack some practical methodology. Thanks in advance!