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.
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
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.
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!!!