Categorize Bar Chart

[spicerol]

Hi,

I have information I would like to group by Month (This is January), Institution (these will all be different), and commercial or medical categories.

How do I get it so that commercial and medical are different colours (preferably blue and red)?

Also, I have uploaded a chart I have found from a different website. I would love to learn how to make my excel chart like this one, where I am shifting through the different months by pressing an up or down button.

Thanks in advance,

spicerol

[dflak]

I can't see the pictures, since I can't see PNG files posted in this forum. I assume you want bar charts just like shown, but only in two colors. I am looking at the source data and I do not see where commercial or medical is shown.

[spicerol]

Hi,

The booklet I attached before is not my data. It is what I would like to make my data look like but am still having trouble.

Here is an attached sample booklet of my data. Any ideas of how to categorize as in the previously attached document?

Cheers,
Spicerol

[dflak]

I will give a more detailed write up this evening. The solution depends on named dynamic ranges and their use in charting. Here are a couple of articles that help with that.

http://www.utteraccess.com/wiki/inde...Dynamic_Ranges
http://www.utteraccess.com/wiki/inde...namic_Charting

[dflak]

I put all the stuff on one page so it’s all together and easier to see.

I’ll start with the two helper columns (F:G). We need two series for medical and commercial so they can be plotted. NA() yields the #N/A error that doesn’t get plotted. You can also set up charts not to plot blank cells.

Next comes the “spinner.” This comes from the Devolper’s Tab. File -> Options -> Customize Ribbon – click on the Developer box. Click on the Insert (tool box) icon.

Select either the spinner (up and down arrows) or scroll bar (left and right arrows). I chose the scroll bar. Once you select it, you can draw it on the screen. Hint: holding the ALT key while drawing will snap the edges to the grid.

Right click on the spinner and fill out the box that comes out. Link the cell to a cell. In this case Cell K1. As you move the pointer back and forth, the number in Cell K1 changes.

Cell L1 looks up the month value based on the array in Column O. Cell L2 finds the row in which the month in Cell L1 is found in Column A. Cell L2 counts up the number of times the selected month appears in Column A. These numbers are used in a named range called Plot_Month.

There are three other names, Y_Axis, Medical and Commercial.

The previous post contains information on named ranges.

Then I created the chart using fixed ranges and then right clicked and selected Select Data and substituted the dynamic named ranges for the fixed ranges.

The final touch was to select the header and in the formula bar, type = L2 so it will display the selected month.

You can recreate these steps yourself or you can *cut* and paste the various cells; the formulas should be able to keep up.

[JosephP]

you could drag the department field to the legend fields area

[spicerol]

Thanks dflak,

I have done everything, including defined the named ranges, but I still can't seem to link the graph with the scroll. I know you were describing it by saying "Then I created the chart using fixed ranges and then right clicked and selected Select Data and substituted the dynamic named ranges for the fixed ranges".

So I created the chart using the fixed ranges, but how do I substitute in the dynamic named ranges?

[dflak]

See this article: http://www.utteraccess.com/wiki/inde...namic_Charting.

Basically Right Click the chart, select Select Data and then Edit the series and axis. So where it says something like 'Sheet name'!X2:X100 change it to read 'Sheet name'!Named_Range.

[spicerol]

Thank you so much! It worked out beautifully! I don't quite understand how the OFFSET function works, but it is a sweet function!

[dflak]

This article http://www.utteraccess.com/wiki/inde...Dynamic_Ranges should give you an idea how offset works.