Hi all,
Probably, like most users, I have signed up with a question in mind. I understand the basics of Excel but always keen to know more! i am using office 2011 (home and business)
I'm currently creating a multi sheet document, which audits our PCs within the office;
Sheet1 = User info including what machine they use.
Sheet 2 - 12 = individual machine info - hardware and software versions etc.
Sheet 13 = index, which is updated with the current software versions etc.
I want to be able to open sheet 1 and it to show me what machines require attention. The easiest way I thought that this could happen is by using the index page to set the 'base' values for the conditional formatting elsewhere in the document.
I have a few questions regarding the above, which I have listed below;
Sheet 13 - Using '=TODAY()' to show machine age
We use MACS at work, they are usually classified as either Early, Mid or Late during that year of manufacture.
I have set the 'TODAY' function to E1 and else where on the sheet I wish to convert the current date to show Early, Mid or Late with the year.
For example 04/02/15 = Early, 2015.
I have managed to put something together like this "=IF(AND(MONTH(E1)<4),"Early")" which does the early bit but I can't figure out a way for it to show the year too?
Also by splitting the year into 3 then do I have to repeat the above two more times to show the ranges 1-4 5-8 9-12 OR can I slim this down to something more elegant by using CONCATENATE?
How to link conditional cells to other sheets?
By using the above I want to create a conditional cell to show that the machine is getting old. Maybe 4 years it shows as orange and at 6 it shows as red. Could anyone advise?
Now, this maybe getting a bit complicated but if I had say 3 conditional cells for each machine 1 for hardware, 1 for OS and 1 for software versions. Is it possible to then link those 3 to the main page into 1 conditional cell that reacts if 3 out of 3 are green then green, if 2 out of 3 or green then yellow, 1 out of 3 then red.
Firstly - is it even possible?
Secondly - can anyone help me do it?
Thank you very much for reading, I hope someone can help!
Will
Probably, like most users, I have signed up with a question in mind. I understand the basics of Excel but always keen to know more! i am using office 2011 (home and business)
I'm currently creating a multi sheet document, which audits our PCs within the office;
Sheet1 = User info including what machine they use.
Sheet 2 - 12 = individual machine info - hardware and software versions etc.
Sheet 13 = index, which is updated with the current software versions etc.
I want to be able to open sheet 1 and it to show me what machines require attention. The easiest way I thought that this could happen is by using the index page to set the 'base' values for the conditional formatting elsewhere in the document.
I have a few questions regarding the above, which I have listed below;
Sheet 13 - Using '=TODAY()' to show machine age
We use MACS at work, they are usually classified as either Early, Mid or Late during that year of manufacture.
I have set the 'TODAY' function to E1 and else where on the sheet I wish to convert the current date to show Early, Mid or Late with the year.
For example 04/02/15 = Early, 2015.
I have managed to put something together like this "=IF(AND(MONTH(E1)<4),"Early")" which does the early bit but I can't figure out a way for it to show the year too?
Also by splitting the year into 3 then do I have to repeat the above two more times to show the ranges 1-4 5-8 9-12 OR can I slim this down to something more elegant by using CONCATENATE?
How to link conditional cells to other sheets?
By using the above I want to create a conditional cell to show that the machine is getting old. Maybe 4 years it shows as orange and at 6 it shows as red. Could anyone advise?
Now, this maybe getting a bit complicated but if I had say 3 conditional cells for each machine 1 for hardware, 1 for OS and 1 for software versions. Is it possible to then link those 3 to the main page into 1 conditional cell that reacts if 3 out of 3 are green then green, if 2 out of 3 or green then yellow, 1 out of 3 then red.
Firstly - is it even possible?
Secondly - can anyone help me do it?
Thank you very much for reading, I hope someone can help!
Will
