Bar or column chart

[emmatm]

I am trying to produce a chart possibly a column chart where I have 5 sets of office types and I want 2 bars per type to show - total desks in area & total desks used. For each 5 office types I want the bar colours to be the same for example red & blue then a space with another set of red and blue bars (I also want each set of bars labeled with the office type).

 

[Asheesh]

hi,

Can you share a sample file please

 

[emmatm]

Hi Asheesh,

I have worked a bit on this since posting my original question so here is my attached file. The chart on the third tab is being pulled form my data tab which is using a dropdown from the working tab.

My question now is on my data tab if B4 results a G ref this is a group so I would want the data below to group the numbers from F-O on the working page so as it shows now anchor desks allocated should show 407 not 204.

Also the chart how do I link the title currently shown as 'xxx' I want this to show as the heading of B1 on the data sheet.

Thanks

 

[emmatm]

Can you see my file?

 

[emmatm]

Sorry file attached now

 

[vletm]

@emmatm
Check this ... I made some changes (dropdown) and 'xxx'-link.
Could You make that change
' if B4 results a G ref this is a group so I would want the data below to group the numbers from F-O on the working page.'
to the working page.
It would be more clear to see and test the results.

 

[emmatm]

Ok, so I have changed my dropdown to select a different business line tier to 'BW technology', if you see on the working tab rows 5 & 6 they both have G1 in column D. So on the data tab where B4 shows a G ref I want the numbers in the table below to combine these so cell B7 should show 407 which is cells F5 & F6 on the working tab.

I have also added a pie chart onto the chart tab, I would like this to show only allocated and occupied but don't know how to switch this.

thanks

 

[vletm]

@emmatm
1) take 'floor' away from [B7] like:
=SUMIFS('Working '!$F:$F;'Working '!$B:$B;Data!$B$2;'Working '!$A:$A;Data!$B$1;'Working '!$D:$D;Data!$B$4)
2) something like this?

 

[emmatm]

My bar charts on tab 'Chart G' I would like it be broken out by building & floor, so on the attached working tab rows 40 & 41 are combined on the chart, I would like these to still show the desk types that are listed out on the data tab cell A7-A10 but grouped by building an floor which is shown on working tab columns B & C.

I want the pie charts to remain as they are.

 

[vletm]

@emmatm
There would be few 'things' ... (vlookup gives 'not so good answer')
Would You check from this version Your two choices,
rows 40 & 41 from 'Working' ( helps [J15]&[J28] )
Are those right choices?
Could You highlight with two color areas that You mean to show on 'Data'?
After those, it'll be more clear to do 'Chart G'
(there're also double '5 Quay Way 04' marks,)

 

[emmatm]

If I choose a business line from column A on the working tab, as you can see some of these appear in either different buildings column B or/and different floors column C.
So what I want if this happens is that I would like my bar chart to show the number of desks in columns F-O on the working page to sow by each building and floor at the moment I am only pulling over a single line of data or it is grouped by columns D or E.

So I think I need my bar chart a bit more complex than it is.

 

[vletm]

@emmatm
Check 'Data2' ...