Advanced Filter Question

[DD999]

I'm trying to create a "visual scheduler". I have a series of narrow columns each of which represents one day in a calendar.

Down the first column I have a list of names of people who will be assigned to projects on specific days.

Above all of this I have several rows that show the project name and a distinctly colored bar for each project.

The idea is that for each project a person is assigned to you will just color fill in the range of dates that they are assigned to it using the format painter.

So far so good. Now what I want to do is have some way of filtering this list so that I can see just people who are working on a particular project. This is where I realized that just formatting the cell with color wouldn't be enough.

I've tried putting a unique number in each colored cell and then setting the font color to be the same as the fill color so it doesn't show. Then I tried filtering on that number for the range of the schedule.

Unfortunately it doesn't seem to be working.

Any suggestions on whether it's possible to make this work?

 

[klmorgan]

You are probably falling into the trap of laying out your data in the way you want to see it rather than keeping the data in tables.

By taking more of a database approach would allow you to build up up reports that can be filtered.


Regards

Keith

 

[DD999]

Hi Keith,

I think you're right. The more I think about it a filter probably isn't the best method to do this.

I do need to keep this spreadsheet in this particular layout for the visual aspects, but maybe I should use command buttons instead of filtering?

It's been years since I've done any real programming and none in VB. Could you tell me if the following is easily possible?

Create a command button for each project.

Whenever a button is pushed the range of rows with the schedule information is checked cell by cell to see if any of the cells have a value that equals the project number. If any cell in the row does equal that value, then the row is displayed. If no cell equals that value then the row is hidden.

Is that possible? Any idea on what kind of complexity is involved.

Thanks again.

 

[klmorgan]

Sorry but thats not an area I'm good at but some of the other bright guys might come up with a way.

The best thought you could use a multiple if statement in a spare cell to the right of the matrix to be say Y if any of the cells contents match a cell with the project code you want then sort on that cell.

or


You could use a cell to enter the project value you are searching for and then use conditional formatting to say make the matching cells Red or Red Text and the others Black or black text for instance.

Regard Keith