Use color or marked cells to make a chart

[NealMed]

We have an excel 2002 shared workbook that we use to keep track of our
capacity for incoming patients. The cells are color coded for each different
insurance.
How can i build a chart to show how many of each insurance we have taken in
each week.
The cell range looks like this. ='2268 SEATTLE'!$B$5:$H$13,'2268
SEATTLE'!$B$17:$H$25,'2268 SEATTLE'!$B$35:$H$43,'2268
SEATTLE'!$B$47:$H$55,'2268 SEATTLE'!$B$65:$H$73,'2268
SEATTLE'!$B$77:$H$85,'2268 SEATTLE'!$B$95:$H$103,'2268 SEATTLE'!$B$107:$H$115
We would like to be able to keep track of each one. Can this be done?

 

[Jon Peltier]

And let me guess, you manually color code the cells as they are populated?
Why not add a column with the name of the insurance company? You can then
sort by that column, or filter the data, or make a pivot table to show what
you want to show.

- Jon

 

[NealMed]

You are correct that the colors are decided as the patients are populated, I
thought about choosing columns by insurance, however, because it's designed
to be populated by date, much like scheduling appt's. That makes it very
difficult to design that way. I thought of placing something like * in
certain catagories that i'd like to build the graph from, is that possible?

 

[Jon Peltier]

Populated by date - do you mean as a calendar? The way it should be done is
in a flat table, with columns for all the pertinent information, including
insurer and date/time. One sheet would populate a calendar showing
appointments by date and time, another would filter by other criteria for
whatever chart you need.

Putting a marker like an asterisk in a cell that contains other data is not
a good idea, because it makes the indicated cells difficult to search for
(more difficult than finding a notation in a field of a flat table).

- Jon

 

[NealMed]

Jon,
I thank you for corresponding with me, I think that it may be easier if you
see what i'm showing you, so that you will have an understanding of what i'm
working with,
would you permit me to email you a copy of the worksheet i'm working with,
so that you might give me suggestions on how to do what i'm asking or how to
make some changes to assist me with that goal?
Thank you.
Neal.

 

[Jon Peltier]

Neal -

Sure, send the file. I can't promise I'll get to it immediately.

- Jon