Hello everyone
I am self-taught at Excel and my learning tools have been trial and error along with internet searches on how to do something in particular. This is the first time I cannot find an answer that helps me so joined this community to see if someone knows a better way or how to get this file to open in a reasonable time frame.
I am using Excel 2010, all updated. I am trying to make a training matrix. I have several tabs and at the top running from left to right is a document number and doc title. On the left, running down are names of people. Each tab is a department so everyone in the department is listed. Let's say Employee X was trained on document 1, I would find the cell that matches and put in the date they were trained. That is all simple and straight forward. What I need the file to do is when it is within 30 days of one year of that date, turn the cell yellow. One year and one days past the year, turn the cell red. I got that figured out using conditional formatting.
The problem is, each cell has to have that CF in it. I did a count of cells, total for all tabs and have 11,125 cells with 2 conditional formats in them. Once I started to get a lot of these cells done, the workbook started taking longer and longer to open. The file is just under 5 MB, so still small. It take up to 3 minutes to open now. I had someone else open the file and it took as long. I sent it home and tried on my PC, which just upgraded to an 8 core processor, SSD HD, and all of that and still took a while. I believe it is because it is recalculating each cell when opening.
I disabled auto-recalculations but made no difference. I believe I read that it doesn't shut off CF anyway. I have spent some time making this file but if opening it takes this long, then others probably won't use it and they will need to. I don't know if there is another way to have the cells highlight based on dates past other than maybe a macro, which I don't know macros. Is there an easier way to have them highlight than setting up CF in each and every cell? I tried to do a grouping of cells but the highlighting only works based off of the first cell so that doesn't work.
I need either a different way so it speeds up the opening issue. It will have to auto calculate so when another manager opens the file, he/she can just look for the yellow and red cells so they know who need the yearly training for that month, plus which document/s to train.
Any and all help will be appreciated, especially after spending a week on this thing. Thank you very much!!!
Oh, forgot to mention that when I copy a cluster of cells and paste, it will sometimes lock up the workbook. Even happens if I copy one cell, then paste it 5, 6 or 7 times. This work computer is new and judging by the specs, it should open just fine.
Thank you again!!!
Bookmarks