Separate column chart bar colors

[dquack]

I am in the Army and we fly helicopters. Part of my job is to ensure that each aircraft flies a certain amount to stay in a productive flow. I created a new chart this morning and was hoping to change the colors of different aircraft. I have 3 flight companies and would love each company to have its own color on the chart. I have attached my report for all to look at if they are wondering. My chart data is pulled from the info tab. The info tab is constantly organized from highest hours to lowest. Because of this the individual aircraft may jump around on the order of the tab. I would like to highlight each aircraft with a specific color and have it transfer over to the chart. Is this even possible? I am trying to get this setup so the other Soldiers won't have to mess with anything. Thanks.

[Andy Pope]

How do you know which company the #Serial belong to?
Or where would you expect to put the colour information for each #Serial

[dquack]

Andy - If you click on the 2nd tab the serials are arranged by company. I am not sure how I would tie the color into the graph. I was wondering if it is even possible or too complex. I have a basic grasp of graphs and was hoping it might be something simple to tie them together, but as we continue to play with them we are fidning a million ways that won't work.

[Andy Pope]

Due to the use of merged cells in naming the companies it would be simpler to use a lookup table to determine serial# / company relationship.

Once you have that you can use multiple series to plot each company.
Use formula to determine whether the data value or #N/A should be use for each category. You can then format the Companies are required.

I have also added an additional series to plot a line that goes from height to lowest value.

[dquack]

Andy,

WOW! That is perfect. I would love to learn all of that. The only issue I saw was the actual company serial numbers are in column C (phaseflow tab) not column K. I tried to readjust things myself, but I guess I don't have a grasp on how you organized it. I would love to learn - excel is awesome and has so much potential.

[Andy Pope]

I'm not sure how you are relating your information.

As I see it now,

Phaseflow!A2:A19 contains Company Name
Phaseflow!C2:C19 contains Aircraft Tail Number
Phaseflow!F2:F19 contains Hours to phase

Phaseflow!K2:K19 contains Serial#

According to the sheet within that table the left side does not relate to the right. i.e. AircraftTailNumber in C2 does not related to Serial# in K2

In fact I'm not 100% sure the data is correct as on the INFO sheet the Serial# is static value and the HRS to Phase is linked to cells in Phaseflow!F2:F19, which have then been ranked.

Once I understand how the data should be related it should be possible to update the lookup table

[Andy Pope]

Still not sure this is fully correct but I have used the Hours to Phase information to identify the Company.

See if this version is more like what you expected.

[dquack]

Andy,

All of your points above are correct except for K2-K19. The K column is set to order itself according to the order of hours till phase on the Info tab. The serial numbers in K2 will adjust accordingly when the data is changed on the info tab.

The new data goes into the Pasted daily tab. Then it is pushed to BN CDR (H10-H33), Phaseflow (D2-D19). Phaseflow (F2-F19) is subtracted from the 2 previous columns. This gives us the true hours till phase. The Info tab (B2-B19) takes its data from the phaseflow tab column F. As the hours change daily we sort F2-F19 from largest to smallest to get the correct arrangment of aircraft by hours till phase. The serial numbers in Info A2-A19 get sorted when we select expand the sort option. I know this is probably a very brutish way of getting the info, but my grasp of excel and formulas are very basic and I made due with what I had. Here is a quick explanation -

Pasted daily tab - new info pasted in every morning from an outside source.
Info tab - Shows the aircraft by order of hours till phase from largest to smallest.
Phaseflow chart tab - Self explanatory (only has A company, C MED, and G 52nd info).
Phaseflow tab - Left of the black line shows current aircraft data (most data is pulled/mirrors from other tabs). The right side of the black line shows the order of the aircraft coming into phase. This data is pulled from the info tab and the order always mirros the info tab.
BNCDR tab - shows all the data for every aircraft in our unit. Half of these aircraft do not factor into the other tabs.

Hopefully this explains or answers some of your questions. While playing with the newest version I changed B8 (071) to 13 hours and sorted (and expanded). When I did this sort I lost the corresponding data in C8. When I went into the chart 071 had moved to the right place by did not have the hours bar due to the #N/A info in C8.

I really appreciated your help. I want to learn all of this and become excel savy. I really appreciate you taking your time to help me out - not many people would do that I and I am thankful for it.

[Andy Pope]

I can not locate the STATSORT routine in order to see what actually happens in order to mess the other information.

And I'm not sure of the change to data you are describing.
The values in INFO!B2:B19 are used when trying to find their matching value in INFO!L2:L19. The values in INFO!L2:L19 are linked to the values in =PHASEFLOW!F2:F19.
So unless you changed the data of the cell referenced by C8 the two values will not match.

It may help to post your revision to the workbook.

[dquack]

Andy,

Okay I see what you mean. When I changed the value in the info tab your additional info was not pulling the correct data across. I had forgotten for a minute that I had to manipulate the actual data not halfway through the stream. Sorry for the confusion. I will look over the sheet tonight and tomorrow and understand your formulas and methods. That way if I ever have to add an aircraft in I will be able to do it without messing up the entire form. Thanks for the awesome help. You sir are a gentlemen and a scholar.