Dynamic Drop Down List Using OFFSET With Color

I don't understand what your Legend looks like. If I see your file (see request above) I think it will be a lot clearer.

You must mean that you have Excel 2003, because the CF restriction was lifted in 2007. I can show you how to do it with macros if you provide the spec for what colors are mapped to what values.

Ok, I have 2007 version of not 2003. I colored the list in the "Data" sheet and what I want to accomplish is when selecting a day for an employee the list comes up from the Data sheet which it does and what I select it color codes it to what it is. The only thing I don't have colored is the "X" which means they were here and a "'----" which means its the weekend or were off.

Now I see exactly what you're doing and how you want it to work. Except I still don't understand your question in #4 above.

Since you already have macros I have provided a macro solution that will use whatever color scheme you define in the Data sheet to color the cells. (You cannot do that with CF). If you change the color scheme on Data, however, it will not automatically go back and re-color cells that are already colored.

BTW your file is small enough that you don't have to zip it to attach it.

Originally Posted by 6StringJazzer

Now I see exactly what you're doing and how you want it to work. Except I still don't understand your question in #4 above.

What I was trying to explain in the end was lets say I added to the data list or changed it. Is there some way I can have the Legend on each employee change as well? Kinda like if i was using MS access tables on a form and updated the table.

Just use formulas in the Legend to refer the list, see attached.

Thank you 6StringJazzer that works perfect! Can you show me where the macro is that changes the colors ect, I cant seem to locate it....

In the Worksheet_Change Subs in modules for sheets "Employee 1" and "Employee 2"

6StringJazzer, I have a question. Since I have to make a new calendar every year I wanted to try and set it up like a calendar but how I have it. anyway, my question is I went on to MS website and downloaded a excel calendar I thought I could work with but every date says #NUM!. It uses an array and im in no way familiar with that. it goes like this...

=Days+1+DATE(Calendar1Year,Calendar1MonthOption,1)-WEEKDAY(DATE(Calendar1Year,Calendar1MonthOption,1),WeekdayOption)

WeekdayOption is each day of the week in the month and that's where the #NUM! is coming from.

I downloaded the template from your link and and I am not having a problem with it. It defaults to start in the current month. What did you change after you downloaded it?

Array formulas are very powerful but not well documented by Microsoft. There are some good resources out there for learning about them. Here is mine but there are some others that are better.

This is what it looks like to me:

calendar.jpg

That's weird I didn't do anything and I downloaded it twice. I did get one to load but when I changed the year, day or something it went back to the image below and the other times it just loos like this.