+ Reply to Thread
Results 1 to 114 of 114

EXCEL - Cell Color Background (SHADING) using IF (statements)

  1. #1
    Forum Contributor wedzmer's Avatar
    Join Date
    06-13-2014
    Location
    Philippines
    MS-Off Ver
    2016
    Posts
    422

    EXCEL - Cell Color Background (SHADING) using IF (statements)

    Hello!

    I'm a Public School Teacher and our government has issued a new school form type for attendance sheets. So I'm really hoping you could help me here. I have attached school form below on "sheet 1" and "sheet 2" is my attendance sheet which I encoded last year.

    The instructions for checking the attendance using sheet 1 are as follows:

    1. If student is absent during morning period "shade" the upper part of the cell of that day.
    2. If student is absent during afternoon period "shade" the lower part of the cell of that day.
    3. If student is absent during the whole morning and afternoon period, DO NOT SHADE but instead put another diagonal line to across the other side to make an "X" for that cell date.

    This was supposed to be done manually using pen and paper (after printing the school form). So I thought of improvising with the use of my old attendance sheet last year. I thought that if student is absent in "sheet 2" during morning period on that day, then it will automatically format that specific day on sheet one with cell color background and so on and so fort. But I'm not good with the coding and also the cell background color using the fill effects can not produce an equally divided 2-colored cell without fading effects. for example: afternoon.png morning.png

    I hope someone could help me here.. I've tried browsing to other threads within the site using conditional formatting with pictures and fill colors but I can't get what I'm hoping to accomplish.

    thank you very much and god speed.


    -wedzmer-
    Attached Files Attached Files

  2. #2
    Forum Contributor wedzmer's Avatar
    Join Date
    06-13-2014
    Location
    Philippines
    MS-Off Ver
    2016
    Posts
    422

    Re: EXCEL - Cell Color Background (SHADING) using IF (statements)

    and by the way, please do not make me use VBA or MACRO codes, I'm really not familiar with it.. I'm only a noob in excel and have been relying mainly on formulas which I read through the site and youtube.

    thank you

  3. #3
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: EXCEL - Cell Color Background (SHADING) using IF (statements)

    Hi

    I know of no way to achieve the effect you want with standard Excel functionality, and I doubt there is anything simple even using VBA, partly because I don't believe Excel will permit a cell to be half coloured along a diagonal.

    Apart from your stated requirement using the layout of sheet 1, what's the real essence of the information you want presented. Is it to see every day even though in the normal course of events absences will be in a minority, or is it to understand the exceptions where a student IS absent and only the exceptions are important. In which case a different layout may be better and allow some sort of colour identification.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  4. #4
    Forum Contributor wedzmer's Avatar
    Join Date
    06-13-2014
    Location
    Philippines
    MS-Off Ver
    2016
    Posts
    422

    Re: EXCEL - Cell Color Background (SHADING) using IF (statements)

    Thank you for response Richard..

    Though the truth is, Sheet 1 is the layout which is used nationally by all public school teachers starting this school year where shading the upper or lower half of that specified date should determine whether the student was absent or not. Doing this using pen and paper would be quite bothersome and befits the point of using school forms from excel when we can't utilize the use of computers even more. That is why I opted to integrate it with sheet 2 which was my old attendance sheet last school year. I don't have much of a choice with the layout design for sheet 1 'coz that's gonna be the same all through out the country. But I have a choice on how the shading of absences should be done. For such reason I wanted it from excel as well.

    About the half coloured along the diagonal, I was trying to utilize the 2 color format using black and white but the fading part doesn't give the supposed half colored effect as was instructed.

    Quote Originally Posted by Richard Buttrey View Post
    Hi

    I know of no way to achieve the effect you want with standard Excel functionality, and I doubt there is anything simple even using VBA, partly because I don't believe Excel will permit a cell to be half coloured along a diagonal.

    Apart from your stated requirement using the layout of sheet 1, what's the real essence of the information you want presented. Is it to see every day even though in the normal course of events absences will be in a minority, or is it to understand the exceptions where a student IS absent and only the exceptions are important. In which case a different layout may be better and allow some sort of colour identification.

  5. #5
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: EXCEL - Cell Color Background (SHADING) using IF (statements)

    Hello wedzmer,

    Welcome to the Forum.

    As Richard Buttrey, has explained, it is impossible for Excel to meet your requirements.

    The best I could do, is to allow you to manually copy and paste the shadings, where applicable.

    On Sheet 1 of the attached sample Workbook, select the cell directly below the Shape you want to Copy, and then press the Up-Arrow once. Now go to the Ribbon, and click on Copy, then select the cell where you want to paste it to, and then Right click on your mouse and select paste, or you may again go to the Ribbon, and click on Paste.

    Hope that helps.

    Regards.
    Attached Files Attached Files
    Please consider:

    Be polite. Thank those who have helped you. Then Click on the star icon in the lower left part of the contributor's post and add Reputation. Cleaning up when you're done. If you are satisfied with the help you have received, then Please do Mark your thread [SOLVED] .

  6. #6
    Forum Contributor wedzmer's Avatar
    Join Date
    06-13-2014
    Location
    Philippines
    MS-Off Ver
    2016
    Posts
    422

    Re: EXCEL - Cell Color Background (SHADING) using IF (statements)

    Hi Winon!

    Thanks for your response.. I actually liked your idea of it.. But I was thinking, would it be possible to assign a condition per cell on sheet 1 such that if the same date on sheet 2 has "A" then the picture file will show for morning (or afternoon) and both are "A" then the X picture will appear; and if no variable/text is encoded on that date then no action should happen on the same cell on sheet 1?

    Quote Originally Posted by Winon View Post
    Hello wedzmer,

    Welcome to the Forum.

    As Richard Buttrey, has explained, it is impossible for Excel to meet your requirements.

    The best I could do, is to allow you to manually copy and paste the shadings, where applicable.

    On Sheet 1 of the attached sample Workbook, select the cell directly below the Shape you want to Copy, and then press the Up-Arrow once. Now go to the Ribbon, and click on Copy, then select the cell where you want to paste it to, and then Right click on your mouse and select paste, or you may again go to the Ribbon, and click on Paste.

    Hope that helps.

    Regards.

  7. #7
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: EXCEL - Cell Color Background (SHADING) using IF (statements)

    Hello wedzmer,

    Thank you for the feedback.

    Since you have stated:
    and by the way, please do not make me use VBA or MACRO codes, I'm really not familiar with it.. I'm only a noob in excel and have been relying mainly on formulas which I read through the site and youtube.

    thank you
    I shall unfortunately not be able to assist you any further.

    It might also require some VBA Macros which I cannot imagine how to complete.

    Regards.

  8. #8
    Forum Contributor wedzmer's Avatar
    Join Date
    06-13-2014
    Location
    Philippines
    MS-Off Ver
    2016
    Posts
    422

    Re: EXCEL - Cell Color Background (SHADING) using IF (statements)

    Really? That's very unfortunate?

    Does anyone else know how to insert a picture using an "if condition"?

    Like for example, cell A1=1 then it makes cell B1 in sheet 2 show picture1. If cell A1=0 (or empty), then no picture in cell B1 in sheet 2 shall show.

    I thought it would be like this in sheet1:

    if A1=1 and A2=0, show picture 1 in B1 sheet 2.
    if A2=1 and A1=0, show picture 2 in B1 sheet 2.
    if A1=1 and A2=1, show picture 3 in B1 sheet 2.

    Quote Originally Posted by Winon View Post
    Hello wedzmer,

    Thank you for the feedback.

    Since you have stated:

    I shall unfortunately not be able to assist you any further.

    It might also require some VBA Macros which I cannot imagine how to complete.

    Regards.

  9. #9
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: EXCEL - Cell Color Background (SHADING) using IF (statements)

    Actually Winon's idea prompted me to put his technique in a macro. See the attached.

    Select a cell and then either Ctrl-l for the top left colour, Ctrl-r for the bottom right, and since Ctrl-x is a reserved shortcut Ctrl-z for the X shape.

    It's important that all the columns and row heights are the same as the cells on row 1 that contain the shapes. You could no doubt keep the shapes in some hidden cells out of the way.
    Attached Files Attached Files

  10. #10
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: EXCEL - Cell Color Background (SHADING) using IF (statements)

    @ Richard Buttrey,

    Hi Richard,

    I like what you have done.

    Once again, it confirms that this problem could only be solved with VBA Macros!

    I was working more or less on a similar approach that you have posted, and using some of your input, I came up with the following, as per the attached sample Workbook.

    Instead of populating the whole Cell with the Shape, I have opted to only Paste the triangle, which in itself requires abusing the "Select" function.

    The only caveat is to determine whether it is am, or pm.

    Perhaps one could work that into the Formulae on Sheet1?

    Any ideas about that?

    I think that maybe I may have a solution for that, but I would appreciate your valuable input as well.

    Best Regards.
    Attached Files Attached Files

  11. #11
    Forum Contributor wedzmer's Avatar
    Join Date
    06-13-2014
    Location
    Philippines
    MS-Off Ver
    2016
    Posts
    422

    Re: EXCEL - Cell Color Background (SHADING) using IF (statements)

    Thanks for your replies!

    The ctrl+l,r, and z works!

    But would there be any other way to automatically generate these images to sheet 1 whenever the variable "A" is put into that certain cell on sheet 2?

  12. #12
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: EXCEL - Cell Color Background (SHADING) using IF (statements)

    Hello wedzmer,

    The attached revised sample Workbook, is the best one can make of your requirement, by adding some Buttons. On Sheet 1, Click on the Button Top Left of the Sheet, and it will up-date automatically. There is also a Reset Button that will show, should you wish to clear the Cell Shapes/Colors, to update Sheet 2, and start again.

    On Sheet 2, I have also provided you with a button to Clear both the attendance Sheets 1 and 2, for you to start with a new month.

    Hopefully, you can make do with as it is now.

    Regards.
    Attached Files Attached Files
    Last edited by Winon; 06-27-2014 at 08:06 PM.

  13. #13
    Forum Contributor wedzmer's Avatar
    Join Date
    06-13-2014
    Location
    Philippines
    MS-Off Ver
    2016
    Posts
    422

    Re: EXCEL - Cell Color Background (SHADING) using IF (statements)

    Oh man!!! You're a genius!!

    How did you do that???

    I really need to know coz I have to do this every month and I also have to put a header on the top page and the buttom page too...
    I only posted the sample worksheet and that doesn't include the whole texts within the file.. In other words, I have to use the things that you did here to the original file where these stuff should work. And by the way, the colors for the shading should change too, I need black pictures to represent for the shading...

    I tried to improvise using the indirect function but damn, my computer shuts down after 2-3 minutes of "not responding" status so I stopped using it..Please do have a look at it because this was what I was trying to accomplish. The attachments are the two files where I linked altogether...

    School Form 2 (June).xlsx Calendar Months.xlsx

    And by the way, I changed the variables "A", "P", and "X", to "A", "B", and "C" respectively coz it's a lot easier to remember... A for morning, B for afternoon and C for whole day..

    Quote Originally Posted by Winon View Post
    Hello wedzmer,

    The attached revised sample Workbook, is the best one can make of your requirement, by adding some Buttons. On Sheet 1, Click on the Button Top Left of the Sheet, and it will up-date automatically. There is also a Reset Button that will show, should you wish to clear the Cell Shapes/Colors, to update Sheet 2, and start again.

    On Sheet 2, I have also provided you with a button to Clear both the attendance Sheets 1 and 2, for you to start with a new month.

    Hopefully, you can make do with as it is now.

    Regards.
    Last edited by wedzmer; 06-29-2014 at 09:29 AM.

  14. #14
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: EXCEL - Cell Color Background (SHADING) using IF (statements)

    Hello wedzmer,

    Thank you for the feedback.

    Before we continue, please check out the attached up-dated sample Workbook with regards to changing the shading to Black. By the way, on Sheet 2, you may now continue with your original "A" for am and/or pm Absence. The formulae on Sheet 1 will automatically assign the correct "A", "B" or "C", on Sheet 1, where applicable.

    Please also confirm if you would like the formula to account for "T" - Tardy, as well?

    Regards.
    Attached Files Attached Files
    Last edited by Winon; 06-29-2014 at 11:18 AM.

  15. #15
    Forum Contributor wedzmer's Avatar
    Join Date
    06-13-2014
    Location
    Philippines
    MS-Off Ver
    2016
    Posts
    422

    Re: EXCEL - Cell Color Background (SHADING) using IF (statements)

    Oh my god! You really are a genius!

    but I need to change the image of the X mark into that which I made on the attachment I posted awhile ago.. Can I change that? And yes, please include the formula for the Tardy too...

    And also, I'm pretty concerned with the heading for the file itself coz I really don't know how to make that work. I tried to add more letters and press CTRL+S to save the file but when I open it, it says error or something.


    Quote Originally Posted by Winon View Post
    Hello wedzmer,

    Thank you for the feedback.

    Before we continue, please check out the attached up-dated sample Workbook with regards to changing the shading to Black. By the way, on Sheet 2, you may now continue with your original "A" for am and/or pm Absence. The formulae on Sheet 1 will automatically assign the correct "A", "B" or "C", on Sheet 1, where applicable.

    Please also confirm if you would like the formula to account for "T" - Tardy, as well?

    Regards.
    Attached Images Attached Images
    Last edited by wedzmer; 06-29-2014 at 11:02 AM.

  16. #16
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: EXCEL - Cell Color Background (SHADING) using IF (statements)

    Hello wedzmer,

    Thank you for your confirmations.

    How did you do that???
    It is all done with the help of Macros in VBA. I shall need some time to go through it all again, to see how I can make it easier for you.

    In the meantime, relax. I shall get back to you, as soon as I can.

    Regards.

  17. #17
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: EXCEL - Cell Color Background (SHADING) using IF (statements)

    Hi wedzmer,

    I forgot to ask you. Did the Workbook which I have Posted open o.k.?, and was it only after you tried to add some letters, and changing the Workbook Name, that you got the Error Message?

  18. #18
    Forum Contributor wedzmer's Avatar
    Join Date
    06-13-2014
    Location
    Philippines
    MS-Off Ver
    2016
    Posts
    422

    Re: EXCEL - Cell Color Background (SHADING) using IF (statements)

    Yeah, there was nothing wrong when I opened it.. it's after I placed letters on sheet 2 and then saved it and then opened it again where the error popped up like the one I showed in the image.

    By the way, there are actually 7 different school forms that I am working at, and this attendance sheet is called the School Form 2. The names on this sheet will be generated from the School Form 1 which I will link with School Form 2 that's why I will be editing (placing links) from these sheets. I do hope I'm making sense here.

    Quote Originally Posted by Winon View Post
    Hi wedzmer,

    I forgot to ask you. Did the Workbook which I have Posted open o.k.?, and was it only after you tried to add some letters, and changing the Workbook Name, that you got the Error Message?

  19. #19
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: EXCEL - Cell Color Background (SHADING) using IF (statements)

    Hi wedzmer,

    In your replies to any answer on any of your Threads, it is really not necessary to quote, the whole post of the contibutor. If you want to point to something specific though, you may quote that specific text relevant to any point you wish to emphasize or clarify.

    Till later.

  20. #20
    Forum Contributor wedzmer's Avatar
    Join Date
    06-13-2014
    Location
    Philippines
    MS-Off Ver
    2016
    Posts
    422

    Re: EXCEL - Cell Color Background (SHADING) using IF (statements)

    Sorry Sir, I'm really new to this type of website. I'll remember that.

    Thank you so much for your help! I really appreciate it.. And I would really wanna learn how you did all of these too.

  21. #21
    Forum Contributor wedzmer's Avatar
    Join Date
    06-13-2014
    Location
    Philippines
    MS-Off Ver
    2016
    Posts
    422

    Re: EXCEL - Cell Color Background (SHADING) using IF (statements)

    Hi Winon,

    Were you able to alter the heading? If it's much trouble, maybe I could help, just tell me what I need to do coz every time I insert new cells at the top, the images go wrong... And also, how will I make this work with the Girls section below?

  22. #22
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: EXCEL - Cell Color Background (SHADING) using IF (statements)

    Hello wedzmer,

    Were you able to alter the heading?
    You mean the Workbook Name? Yes, that is one of the things I have been working on. You may now change the name to whatever you like, without a problem. We are however limited to a few constraints though.

    We should try to establish the absolute final layout of the Sheets, where it will not be necessary to add or delete Rows, Columns or Cells, which are refered to by the Macro in VBA. If we don't do that, it is never going to work, as we have it now.

    That is why I am sending you the the same "Old" Workbook with changes made as requested. What I have found was that by including the "T" - Tardy Shape, slows down the up-date quite a bit.

    I would appreciate it if you could have a look at it, and also advise whether we may exclude some, or all, of the new additions.

    The actual School Form should not be a problem, including the sheet for the ladies, but first we have to agree on the above.

    Regards.
    Attached Files Attached Files

  23. #23
    Forum Contributor wedzmer's Avatar
    Join Date
    06-13-2014
    Location
    Philippines
    MS-Off Ver
    2016
    Posts
    422

    Re: EXCEL - Cell Color Background (SHADING) using IF (statements)

    Hi Winon,

    Thanks for the new update! I found this much more easier and more worth it to use than the one required from us by our Department.

    What I have found was that by including the "T" - Tardy Shape, slows down the up-date quite a bit.
    I found out awhile ago that the ruling for the "tardy" principle was that if a student was tardy for 4 times, then it should be counted as 1 absent. Nevertheless, in the report I am suppose to produce, we can get away with shading the "tardy" and just sum all of it per row. Anyway we will have a separate report for tardiness and absences.

    We should try to establish the absolute final layout of the Sheets, where it will not be necessary to add or delete Rows, Columns or Cells, which are refered to by the Macro in VBA. If we don't do that, it is never going to work, as we have it now.
    This is the final layout:

    School Form 2.xlsx


    Thanks a lot! I really really appreciate it!
    Last edited by wedzmer; 07-01-2014 at 05:03 AM. Reason: ATTACHED FINAL SCHOOL FORM 2 LAYOUT

  24. #24
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: EXCEL - Cell Color Background (SHADING) using IF (statements)

    Hello wedzmer,

    Thank you for the feedback.

    Please give me a day or so, and I shall get back to you with the newly attached School Form.

    Regards.

  25. #25
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: EXCEL - Cell Color Background (SHADING) using IF (statements)

    Hello wedzmer,

    I have just checked the new School Form, and it shows #Ref in Column B of the female section.

    Would it be possible for you to attached a new School Form without such "error", or a copy of the Sheet that those formulas refer to?

    I think the formulae in that Column refers to an external link, which I do not have access to, making it impossible for me to amend your formulas to check on "Tardy", etc.

    Thank you.

  26. #26
    Forum Contributor wedzmer's Avatar
    Join Date
    06-13-2014
    Location
    Philippines
    MS-Off Ver
    2016
    Posts
    422

    Re: EXCEL - Cell Color Background (SHADING) using IF (statements)

    Quote Originally Posted by Winon View Post
    Would it be possible for you to attached a new School Form without such "error", or a copy of the Sheet that those formulas refer to?

    I think the formulae in that Column refers to an external link, which I do not have access to, making it impossible for me to amend your formulas to check on "Tardy", etc.
    Hello winon,

    I have updated the School Form with a new sheet to avoid the #Ref error in the attachment below...

    School Form 2_Updated.xlsx

    And also, I would like to ask how to add the number of absences for the morning and afternoon per column as indicated in the "Attendance Sheet" in this new attachment.. Because I don't know what is the formula for adding only the morning for the vertical cells like in the range (e5:e66) and on the same range, adding only the afternoon which should be indicated on cells E67 and E68 respectively. And then the sum of these two cells will be the one I should indicate in D43 of sheet SF2 (June).

    Thank you again for the help Winon!

  27. #27
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: EXCEL - Cell Color Background (SHADING) using IF (statements)

    Hello wedzmer,

    Please try your School Form now.

    Regards.
    Attached Files Attached Files

  28. #28
    Forum Contributor wedzmer's Avatar
    Join Date
    06-13-2014
    Location
    Philippines
    MS-Off Ver
    2016
    Posts
    422

    Re: EXCEL - Cell Color Background (SHADING) using IF (statements)

    Hi Winon!

    I just downloaded it and will test it later in the morning!
    Thanks man!!!

    Really appreciate it...

  29. #29
    Forum Contributor wedzmer's Avatar
    Join Date
    06-13-2014
    Location
    Philippines
    MS-Off Ver
    2016
    Posts
    422

    Re: EXCEL - Cell Color Background (SHADING) using IF (statements)

    Hi Winon,

    I tested the new school form you posted and everything was doing perfect... But my problem is when I change the dates for the next month, say July, something pops up like error debug etc. Then when i save it, it wont work anymore...

    The same thing happens when I update the names to refer back to SF1 instead of that which was within the sheet which was SF3.

  30. #30
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: EXCEL - Cell Color Background (SHADING) using IF (statements)

    Hello wedzmer,

    Thank you for your reply.

    It took me a couple of hours to put your school form together, got tired and missed a few "glitches".

    I have already seen, and corrected the issues you came across, and I have also set up the printing formats.

    Please try the attached revised Workbook, and hopefully, it should work now.

    Let me know if you come across any other issues, that I might have overlooked.

    Regards.
    Attached Files Attached Files

  31. #31
    Forum Contributor wedzmer's Avatar
    Join Date
    06-13-2014
    Location
    Philippines
    MS-Off Ver
    2016
    Posts
    422

    Re: EXCEL - Cell Color Background (SHADING) using IF (statements)

    Hello Winon,

    It took me a couple of hours to put your school form together, got tired and missed a few "glitches".
    Sorry for the trouble... I really appreciate your help... And for actually putting all my worries to trash...

    I also forgot to mention that I changed the formula you used to add up the absences coz I noticed you counted each absent as one whereas we count it as 0.5 each..it's the least I can do.. ^_^

    I'll test it all right now..

    And by the way, is there any formula where Excel would count if there are students who actually had 5 consecutive absences?
    Last edited by wedzmer; 07-03-2014 at 09:05 AM.

  32. #32
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: EXCEL - Cell Color Background (SHADING) using IF (statements)

    Hi wedzmer,

    counted each absent as one whereas we count it as 0.5 each
    No problem, as I was not sure exactly how you wanted it.

    Counting 5 consecutive absences?
    That could be quite difficult since the formula would have to cater for a Public Holiday, a weekend, or a long weekend, unless we create a valid School calendar, and use Workdays for School days.

    I would suggest we leave that for later, and first see how the Form plays out.


    Regards.

  33. #33
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: EXCEL - Cell Color Background (SHADING) using IF (statements)

    Hi wedzmer,

    How are you doing with the lastest School Form?

    Regards.

  34. #34
    Forum Contributor wedzmer's Avatar
    Join Date
    06-13-2014
    Location
    Philippines
    MS-Off Ver
    2016
    Posts
    422

    Re: EXCEL - Cell Color Background (SHADING) using IF (statements)

    Hello Winon, sorry for the late response...

    I was busy changing the formula in Sheet 1 because I was wrong in understanding that it should've been counting the number of students present during that said day instead of counting the absences of that day. lmfao.. my bad...

    I also had trouble because I thought I could just copy the worksheet in another sheet on that same file, then name the tab with july, august and so fort..
    I really have to make a different file for each month...

    Anyways, the file is working great! I love it! Thanks a lot!

    That could be quite difficult since the formula would have to cater for a Public Holiday, a weekend, or a long weekend, unless we create a valid School calendar, and use Workdays for School days.
    The worksheet actually had Calendar Days indicating the holidays for this school year as ordered by our education department. I was also about to ask you if it was possible that if these holidays were to occur on a particular month, the file would automatically recognize it and merge all the vertical cells on that day and place the text "National Holiday"?

  35. #35
    Forum Contributor wedzmer's Avatar
    Join Date
    06-13-2014
    Location
    Philippines
    MS-Off Ver
    2016
    Posts
    422

    Re: EXCEL - Cell Color Background (SHADING) using IF (statements)

    Hello Winon, sorry for the late response...

    I was busy changing the formula in Sheet 1 because I was wrong in understanding that it should've been counting the number of students present during that said day instead of counting the absences of that day. lmfao.. my bad...

    I also had trouble because I thought I could just copy the worksheet in another sheet on that same file, then name the tab with july, august and so fort..
    I really have to make a different file for each month...

    Anyways, the file is working great! I love it! Thanks a lot!

    That could be quite difficult since the formula would have to cater for a Public Holiday, a weekend, or a long weekend, unless we create a valid School calendar, and use Workdays for School days.
    The worksheet actually had Calendar Days indicating the holidays for this school year as ordered by our education department. I was also about to ask you if it was possible that if these holidays were to occur on a particular month, the file would automatically recognize it and merge all the vertical cells on that day and place the text "National Holiday"?

  36. #36
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: EXCEL - Cell Color Background (SHADING) using IF (statements)

    Hello wedzmer,

    Thank you for the feedback.

    I really have to make a different file for each month...
    No, you don't need to. On the sheet you want to copy just Right Click on the Tab of that Sheet, and then click on, Move or Copy, another small screen will appear with a check box to the bottom left. Now first confirm which sheet to copy and then Check that Copy Box, then click on (move to end). Now first Select any other Sheet, and then go back to the newly created Sheet, and all will be good to go.

    Give that a try, and let me know if it works on your side as well.

    Regards.

  37. #37
    Forum Contributor wedzmer's Avatar
    Join Date
    06-13-2014
    Location
    Philippines
    MS-Off Ver
    2016
    Posts
    422

    Re: EXCEL - Cell Color Background (SHADING) using IF (statements)

    Hi winon...

    I think i did something wrong with those set of instructions... The new attendance sheet is not working for July (the one i copied sf2 of june altering the month to july).

    Maybe the formulas only recognize the sheet for sf2 june?

  38. #38
    Forum Contributor wedzmer's Avatar
    Join Date
    06-13-2014
    Location
    Philippines
    MS-Off Ver
    2016
    Posts
    422

    Re: EXCEL - Cell Color Background (SHADING) using IF (statements)

    Hello Winon,

    I just sent a copy of the school form 2 on our district and I was told that:

    1. The Tardy/Late should have a corresponding upper/lower shade the same shading for absenteeism.

    2. Upper/Lower shade represents both absents and late so they have the same shading.

    3. The cumulative number of absents are only "C" (student who were absent whole day) disregarding those who were only half day absent.

    Can I use the shading system you used for absents? I mean, if I type "T" in the attendance sheet, it will show the same picture for the absents. So the thing is, the code will both recognize "A" and "T" to show the same symbol for shading (upper for morning, lower for afternoon).

    How do I edit that?


    *edited/additional*

    I realized that if we use the same string for tardiness with absents is that, if a student is absent in morning and afternoon, it would immediately indicate as "C" and gives a cross on the SF2..
    Last edited by wedzmer; 07-07-2014 at 09:25 AM.

  39. #39
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: EXCEL - Cell Color Background (SHADING) using IF (statements)

    Hello wedzmer,

    Sorry for the late response but, I had to sort out a couple of other issues as well.

    Please leave it with me for a couple of days, to attend to it, as and when I have some time to spare, and I shall send you a much better, user friendly solution, after which we may may have to fine tune some formulae, to best suit your exact requirements.


    Regards.

  40. #40
    Forum Contributor wedzmer's Avatar
    Join Date
    06-13-2014
    Location
    Philippines
    MS-Off Ver
    2016
    Posts
    422

    Re: EXCEL - Cell Color Background (SHADING) using IF (statements)

    Hello Winon,

    Thanks a lot for everything! If it's so much trouble, just the update on having tardy/late to have the same representation with the absences. Anyway, the whole day absences "C" will be the ones to count and not the A's and B's; along with the Late/Tardy. That would pretty much do it, in God's will.

  41. #41
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: EXCEL - Cell Color Background (SHADING) using IF (statements)

    Hello wedzmer,

    I have deleted the Attendance Sheet, and incorporated it into the actual School Form Sheet.

    Please see the attached sample Workbook. Buttons are available to Update, Reset, View Legend, View Attendance Register, and Create Copy. The Create Copy Button will automatically create a copy of the School Form and add it after the last Sheet. All you have to do then, is Rename that Sheet to what you want it to be, and all will be fine.

    Please advise if that is what you wanted.

    Regards.
    Attached Files Attached Files
    Last edited by Winon; 07-09-2014 at 09:59 AM.

  42. #42
    Forum Contributor wedzmer's Avatar
    Join Date
    06-13-2014
    Location
    Philippines
    MS-Off Ver
    2016
    Posts
    422

    Re: EXCEL - Cell Color Background (SHADING) using IF (statements)

    Hello Winon!

    Thanks a lot for your help! It's beyond what I expected. The concept you have incorporated was far better than I could have imagined.

    Though I'm having trouble updating the worksheet. Every time I click the Update Button, I encountered an error...

    error.jpg

    What do I do with these? Did I do anything wrong?

  43. #43
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: EXCEL - Cell Color Background (SHADING) using IF (statements)

    Hello wedzmer,

    Click on the Help button in that error message box, and follow the steps as explained in there.

    Let me know if you got it working.

    Regards.

  44. #44
    Forum Contributor wedzmer's Avatar
    Join Date
    06-13-2014
    Location
    Philippines
    MS-Off Ver
    2016
    Posts
    422

    Re: EXCEL - Cell Color Background (SHADING) using IF (statements)

    Hi Winon, I've been redirected here:

    http://msdn.microsoft.com/en-us/libr...v=vs.120).aspx

    I don't know what to download... Should I download everything? Wouldn't that harm our computer?

  45. #45
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: EXCEL - Cell Color Background (SHADING) using IF (statements)

    Hello wedzmer,

    Please try this revised attachment.
    Attached Files Attached Files

  46. #46
    Forum Contributor wedzmer's Avatar
    Join Date
    06-13-2014
    Location
    Philippines
    MS-Off Ver
    2016
    Posts
    422

    Re: EXCEL - Cell Color Background (SHADING) using IF (statements)

    Sir Winon!!!


    You're a Master in MS EXCEL!!!!!

    WOW!!!!

    Totally more than what I needed!!!

    Call me emotional but tears really are literally kissing my cheeks right now... It's like I'm seeing our school program worth it of attention once the department sees this...

    I know this is so much, and I might be pushing myself here, but is there a formula where in the Remarks Section of the Sheet, that if I put "T.O." (transferred out), the new copied sheet would automatically delete that student and move to the next student in the column?

    If there's no formula for that.. I'm very much satisfied with the sheet already... I'll just copy-paste it and tell our admin to do so...

  47. #47
    Forum Contributor wedzmer's Avatar
    Join Date
    06-13-2014
    Location
    Philippines
    MS-Off Ver
    2016
    Posts
    422

    Re: EXCEL - Cell Color Background (SHADING) using IF (statements)

    Sir winon! Hi!

    I would just like to ask if in case I have 40 Boys and then the rows we used were only set for 30 learners, would adding new rows affect the sheet?

    I'm kind of worried and I didn't want to alter the file...

  48. #48
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: EXCEL - Cell Color Background (SHADING) using IF (statements)

    Hello wedzmer,

    would adding new rows affect the sheet?
    Absolutely yes, as I have stated in Post #22, and quoted below.

    We should try to establish the absolute final layout of the Sheets, where it will not be necessary to add or delete Rows, Columns or Cells, which are refered to by the Macro in VBA. If we don't do that, it is never going to work, as we have it now.
    We could consider increasing the file size to cater for any possible maximum number of scholars per section, but then you should tell me what that maximum number should be. Could it be 40, 50, 75, or 100 or more?

    I could build in any final maximum number into the sheet, and then, to make it look less clumsy, we could add two Buttons to the sheet to hide, or to unhide rows that are Empty.

    The most Important thing to remember is not to delete or insert any Rows, except for the Notes or Comments as it is now on Page 3.

    Regarding Post #46,

    is there a formula where in the Remarks Section of the Sheet, that if I put "T.O." (transferred out), the new copied sheet would automatically delete that student and move to the next student in the column?
    A few factors come into play here, which makes it difficult for me to give you a ready Code. Apart from "T.O." there is also "T.I.", and "D.O.", with the names in Column B sorted alphabetically. I don't think this happens on a regular basis, and I would suggest that any such changes be done manually, after a new Sheet was created. It would be easier, more accurate, and can be done quite quickly.

    Regards.
    Last edited by Winon; 07-11-2014 at 04:53 AM.

  49. #49
    Forum Contributor wedzmer's Avatar
    Join Date
    06-13-2014
    Location
    Philippines
    MS-Off Ver
    2016
    Posts
    422

    Re: EXCEL - Cell Color Background (SHADING) using IF (statements)

    Hello Winon!

    Yes, I did thought that there would be some problems when the new condition of T.O, T.I and D.O come into play...i was just asking for an opinion for that... Cause you know more than I do.

    We could consider increasing the file size to cater for any possible maximum number of scholars per section, but then you should tell me what that maximum number should be. Could it be 40, 50, 75, or 100 or more?
    The maximum number of boys and girls per class is 50 each... And I was afraid about the adding of rows. I'm glad i asked you first...

    to make it look less clumsy, we could add two Buttons to the sheet to hide, or to unhide rows that are Empty.
    That was what I was asking on the other post because apart from this worksheet, i was thinking to apply the same principle on the other sheet form i was working on.

    Thanks!

  50. #50
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: EXCEL - Cell Color Background (SHADING) using IF (statements)

    Hello wedzmer,

    With the Workbook (Attendance Test Form 2 -Update3), we are working on, to increase the total number of scholars to 60 for male and female respectively, in Sheet SF3, you are referencing to another Worksheet named, Student Information'!B25, in a Workbook called School Forms 2014.xlsx which is located on your system in a Folder called "School Forms".

    I have not received any Workbook from you that contains a Sheet called Student Information.

    On my side, after I have increased the totals to 60, it now returns a #Ref! Error, which makes it impossible for my to complete all the upgrades to the Workbook.

    Could you please increase the totals to 60 each for male and female of that Student Information Sheet, and send it to me?

    Thank you.

  51. #51
    Forum Contributor wedzmer's Avatar
    Join Date
    06-13-2014
    Location
    Philippines
    MS-Off Ver
    2016
    Posts
    422

    Re: EXCEL - Cell Color Background (SHADING) using IF (statements)

    Hello Sir Winon!!!

    This might be the last problem I have for SF2, I tried to use the formulas found in this new thread I made: http://www.excelforum.com/excel-gene...ref-error.html and it works on all workbooks except SF2, the one you made.. is there something in the Macros that prevent it?

  52. #52
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: EXCEL - Cell Color Background (SHADING) using IF (statements)

    Hello wedzmer,

    is there something in the Macros that prevent it?
    If you change the formulae of the SF2 Sheet, in Column D through to Column AB, the Macro will not work.

    You may however change formulas in Columns A, B, AC and AD, without a problem.

    Regards.

  53. #53
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: EXCEL - Cell Color Background (SHADING) using IF (statements)

    Hello wedzmer,

    Can you please explain to me where and what, you want to change on the SF2 Sheet?

    Thank you.

  54. #54
    Forum Contributor wedzmer's Avatar
    Join Date
    06-13-2014
    Location
    Philippines
    MS-Off Ver
    2016
    Posts
    422

    Re: EXCEL - Cell Color Background (SHADING) using IF (statements)

    I see...i was only changing the column where the learner's name is referenced.. I used this...

    Formula:
    =IF(INDEX('C:\Users\Wedzmer\Desktop\School Forms\Master List\[(2) July - Master List.xlsx]Boys'!$1:$1048576,ROW(),COLUMN())="","",INDEX('C:\Users\Wedzmer\Desktop\School Forms\Master List\[(2) July - Master List.xlsx]Boys'!$1:$1048576,ROW(),COLUMN()))

    But the referencing is not accurate... Could you advice me what's wrong?
    Last edited by wedzmer; 07-17-2014 at 02:44 PM. Reason: wrong formula

  55. #55
    Forum Contributor wedzmer's Avatar
    Join Date
    06-13-2014
    Location
    Philippines
    MS-Off Ver
    2016
    Posts
    422

    Re: EXCEL - Cell Color Background (SHADING) using IF (statements)

    Hi Winon,

    Quote Originally Posted by Winon View Post
    Hello wedzmer,

    Can you please explain to me where and what, you want to change on the SF2 Sheet?

    Thank you.
    Well, it's like this.. I made a Master List Folder that should reference all the names from SF1 of School Forms 2014. The folder contains one workbook per month. So there's this July Workbook I made that used the formula:

    =IF(INDEX('C:\Users\Wedzmer\Desktop\School Forms\Master List\[(2) July - Master List.xlsx]Boys'!$1:$1048576,ROW(),COLUMN())="","",INDEX('C:\Users\Wedzmer\Desktop\School Forms\Master List\[(2) July - Master List.xlsx]Boys'!$1:$1048576,ROW(),COLUMN()))

    Which uses to reference the names of the Boys - Sheet (on that workbook named (1) June - Master List); and everything works there. I also made another workbook for August.

    I was planning to use these workbooks that in case one of our learners opted to drop-out or transfer to another program, then we can just immediately delete the name of that student on the following month (Master List - August) because that workbook would be the source of the SF 2 sheet for August.

    In that sense, our volunteers would not need to alter any part of the SF2 Workbook except the Attendance Register that you designed.

    The problem is:

    It doesn't work on SF2. It should have referenced the name "ALI, SAMAD MONTE" which was the first student on the list instead of displaying the 12th student which was "RAMIREZ, JOHN LLOYD LOPEZ".
    Last edited by wedzmer; 07-17-2014 at 02:52 PM. Reason: clicked the reply button accidentally.

  56. #56
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: EXCEL - Cell Color Background (SHADING) using IF (statements)

    Sorry for the late reply, wedzmer.

    I suddenly ran out of my internet airtime since we were last connected, and I first had to replinish same.

    There is nothing wrong with the formula you are using, except that it uses the Row on which it is, to return any data from the sheet it refers to, on the same Row, i.e. Row 13.

    The solution could be to change the layout of your (1) July-Master List, as shown in the attached sample Workbook, where I have used the (1) June-Master List, for demonstration purposes.

    If that works for you, and if it does not affect any other sheets using the same type of formula, all you have to do, is to change the layout of the (1) July-Master List to be the same as the sample Workbook for (1) June-Master List.

    One VERY IMPORTANT thing to remember is to protect the Master Lists, so that nobody can insert or delete any Rows. That should solve your issue.

    Hope that helps.

    Regards.
    Attached Files Attached Files
    Last edited by Winon; 07-18-2014 at 04:59 AM. Reason: Added extra Workbook for better clarification

  57. #57
    Forum Contributor wedzmer's Avatar
    Join Date
    06-13-2014
    Location
    Philippines
    MS-Off Ver
    2016
    Posts
    422

    Re: EXCEL - Cell Color Background (SHADING) using IF (statements)

    Hello Winon!

    Tomorrow, July 21, will be the day I will present the Integrated School Forms.. I do pray it will work out well and will get the support it needs from the department.

    Thank you very much for your great help and the support of all Excel Forum Admin and members!

    I will be back pretty soon... And hopefully will join the Commercial Services!


    Wedzmer

  58. #58
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: EXCEL - Cell Color Background (SHADING) using IF (statements)

    Hello wedzmer,

    That is very good news!

    I take it then that my last reply to you did seem to have worked, as well.

    Good luck with your presentation!

    Maybe you would now consider marking this Thread as, Solved, also.

    Thank you.

  59. #59
    Forum Contributor wedzmer's Avatar
    Join Date
    06-13-2014
    Location
    Philippines
    MS-Off Ver
    2016
    Posts
    422

    Re: EXCEL - Cell Color Background (SHADING) using IF (statements)

    Hello Winon!


    Yes, I will mark it solved now...

    And about the presentation, well, unfortunately, they did not like the project. They found it complicated though it was made user friendly as it is. What you have produced was very much more than I could have expected. Maybe they just didn't want to support it and they know that our branch would make a good recognition once it goes public.

    Nonetheless, I would like to once again thank you and the site and its members for everything.


    Sincerely,


    Wedzmer

  60. #60
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: EXCEL - Cell Color Background (SHADING) using IF (statements)

    Hello wedzmer,

    Thank you for the feedback.

    Sorry to hear the bad news. Maybe you should have asked them, if they don't approve of it, what better alternative do they have?

    Thank you for marking this thread as Solved.

    Regards.

  61. #61
    Forum Contributor wedzmer's Avatar
    Join Date
    06-13-2014
    Location
    Philippines
    MS-Off Ver
    2016
    Posts
    422

    Re: EXCEL - Cell Color Background (SHADING) using IF (statements)

    Hello Winon!

    Quote Originally Posted by Winon View Post
    Hello wedzmer,


    Sorry to hear the bad news. Maybe you should have asked them, if they don't approve of it, what better alternative do they have?
    Would you believe that they actually told me, "We don't really recommend using formula based files for the School Forms. It's a lot better if it was encoded manually".

    With such response from them.. I don't really have much to work with.


    But thanks for everything sir!

    Wedzmer

  62. #62
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: EXCEL - Cell Color Background (SHADING) using IF (statements)

    Hello wedzmer,

    LOL !!!, I really find that very funny. Or rather sad.

    Makes me wonder why they use formulated petroleum gas for their vehicles. Do they also frown upon Cell Phones, and stay in contact with whoever, via a note tied to a pigeons' neck?

    Maybe it would be a lot better if they did not fill up their vehicles, but rather push it to where they want to go!

    They are holding you back, and stand in your way of doing much better, with their old school of reasoning.

    Good Luck.

  63. #63
    Forum Contributor wedzmer's Avatar
    Join Date
    06-13-2014
    Location
    Philippines
    MS-Off Ver
    2016
    Posts
    422

    Re: EXCEL - Cell Color Background (SHADING) using IF (statements)

    Hi Winon,

    Sorry for the late response. I actually asked for another appointment for them to reconsider the files I submitted. I presented in front of the two ICT Coordinators of our department. I was again bombarded with questions where I got only few of them answered. The hardest one was that when they tested the files on another computer where they said, "your files only work in your computer. When it is transferred into another u it, and saved in the desktop as you presented, the links in your directories fail. This means that a typical lay person without knowledge on links and references would have a hard time editing and reediting the links. This befits your primary objective to make a user-friendly School Forms file which was supposedly ready to use."

    Well, they were right. It really didn't work that time.

  64. #64
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: EXCEL - Cell Color Background (SHADING) using IF (statements)

    Hi wedzmer,

    I see no reason why you would need to have links to the Workbook. Why don't you just incorporate everything into one "Stand-alone" Workbook?

    Are they all running at least Excel 2007 or later editions? If so, you could knock them of their feet with another approach!

  65. #65
    Forum Contributor wedzmer's Avatar
    Join Date
    06-13-2014
    Location
    Philippines
    MS-Off Ver
    2016
    Posts
    422

    Re: EXCEL - Cell Color Background (SHADING) using IF (statements)

    Hello Winon,

    I have two files right, the SF2 (attendance sheet) and the School Forms 2014 where you first incorporated the hide button functions. Then I made a Masterlist Folder which links all the names that should refer the monthly attendance sheets (sf2). I tried to solve the transferred in/out problem by making a Monthly Master List where if there is a student who left or entered, the user will only add/delete a row for that specific student and it will update automatically on the SF2. It did work! (on my computer, but it didn't on their computer). The Office 2007 they were using was promptin over and over asking to edit the links of the workbook. That was where they hit me hard. I was so down actually. I thought it was all okay.



    Quote Originally Posted by Winon View Post

    Are they all running at least Excel 2007 or later editions? If so, you could knock them of their feet with another approach!
    Yes, they were using excel 2007. I think the main office uses 2010. How so I deal with it?
    Last edited by wedzmer; 08-01-2014 at 12:26 PM.

  66. #66
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: EXCEL - Cell Color Background (SHADING) using IF (statements)

    Hi wedzmer,

    Would you be able to incorporate everything into one "Stand-alone" Workbook?

  67. #67
    Forum Contributor wedzmer's Avatar
    Join Date
    06-13-2014
    Location
    Philippines
    MS-Off Ver
    2016
    Posts
    422

    Re: EXCEL - Cell Color Background (SHADING) using IF (statements)

    Hello Winon,

    What do you mean when you say "Stand-alone"? Are you saying putting everything - all the school forms - into one file? wouldn't that be messy? The SF2 alone has 10 months, that's 10 worksheets right?

  68. #68
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: EXCEL - Cell Color Background (SHADING) using IF (statements)

    Hello wedzmer,

    wouldn't that be messy? The SF2 alone has 10 months, that's 10 worksheets right?
    Correct Sir, but we can hide all the "old sheets", and make it visible on request. So, basically only the "Current" applicable Seets will be visible.

  69. #69
    Forum Contributor wedzmer's Avatar
    Join Date
    06-13-2014
    Location
    Philippines
    MS-Off Ver
    2016
    Posts
    422

    Re: EXCEL - Cell Color Background (SHADING) using IF (statements)

    Hi Winon,

    That would be a great idea!

    But before anything else, is there any other way that could actually solve the problem like, the formula with just be adjusted that would be universal in reading the desktop of the user?

  70. #70
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: EXCEL - Cell Color Background (SHADING) using IF (statements)

    is there any other way that could actually solve the problem like, the formula with just be adjusted that would be universal in reading the desktop of the user?
    Sorry wedzmer, but I don't understand what you are asking here.

    If everything is incorporated into one Workbook, and any user loads it on any computer, desktop or laptop, and any of those machines are running on Excel 2007 or later,with Macros enabled, all should be fine.

  71. #71
    Forum Contributor wedzmer's Avatar
    Join Date
    06-13-2014
    Location
    Philippines
    MS-Off Ver
    2016
    Posts
    422

    Re: EXCEL - Cell Color Background (SHADING) using IF (statements)

    Hi Winon,

    Sorry wedzmer, but I don't understand what you are asking here.
    It's like this.. the formula I'm using for the Master List for example is:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    The part where the reference says something like:
    Please Login or Register  to view this content.
    refers to the name of my desktop right? Is there a way where this part would be something like "universal"? I mean, for whatever computer instead of just referring to my own desktop?

  72. #72
    Forum Contributor wedzmer's Avatar
    Join Date
    06-13-2014
    Location
    Philippines
    MS-Off Ver
    2016
    Posts
    422

    Re: EXCEL - Cell Color Background (SHADING) using IF (statements)

    I actually made a different thread for that but even our comembers here are having problems identifying what caused it... Sir please kindly have a look:

    http://www.excelforum.com/excel-gene...ml#post3791382

  73. #73
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: EXCEL - Cell Color Background (SHADING) using IF (statements)

    Hello wedzmer,

    I have received your PM, and since the issue dates back to 2014, including a few confusions, it would be preferable if you could start a new Thread, adding a link to this Thread and some other Threads regarding formulas and other issues culminating from your original Thread, for clarification.

    In your new Thread, could you please indicate how you go about creating a new Sheet for each next month?

    I do not know if you are using "School Form 2F", and I need to know where to make the changes you need, to work with.

    Regards.

  74. #74
    Forum Contributor wedzmer's Avatar
    Join Date
    06-13-2014
    Location
    Philippines
    MS-Off Ver
    2016
    Posts
    422

    Re: EXCEL - Cell Color Background (SHADING) using IF (statements)

    Hello Sir!!!

    Thanks for your immediate response...

    I really don't need much from what you made last time nor any other additional features whatsoever. I just wanna ask you if you could assist me in making the number of students for male and female to be 100 each instead of the origina number which was 50 each only... I actually have more boys this time which was more than the number of rows you made previously.

  75. #75
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: EXCEL - Cell Color Background (SHADING) using IF (statements)

    Hello wedzmer,

    In your new Thread, could you please indicate how you go about creating a new Sheet for each next month?

    I do not know if you are using "School Form 2F", and I need to know where to make the changes you need, to work with.
    So, would you please answer these two questions?

    1. How do you create a new Sheet for each next month?

    2. Please tell me on which Sheet you want me to increase the students totals?

    Regards.

  76. #76
    Forum Contributor wedzmer's Avatar
    Join Date
    06-13-2014
    Location
    Philippines
    MS-Off Ver
    2016
    Posts
    422

    Re: EXCEL - Cell Color Background (SHADING) using IF (statements)

    Hi Sir Winon!

    Thanks again for the response..

    Well, for the first question, I just removed the COPY Button function you did since I already finished making copies till the month of April.
    And for the second question, maybe you could do it for the June, i'll just copy the worksheet and do the necessary editing for the months of July till April.

    I really don't know how much more codes I changed since the last version you made which was:
    (though I only changed the codes within the sheet and no macros since i'm not any good at that)...

    Quote Originally Posted by Winon View Post
    Sorry for the late reply, wedzmer.

    I suddenly ran out of my internet airtime since we were last connected, and I first had to replinish same.

    There is nothing wrong with the formula you are using, except that it uses the Row on which it is, to return any data from the sheet it refers to, on the same Row, i.e. Row 13.

    The solution could be to change the layout of your (1) July-Master List, as shown in the attached sample Workbook, where I have used the (1) June-Master List, for demonstration purposes.

    If that works for you, and if it does not affect any other sheets using the same type of formula, all you have to do, is to change the layout of the (1) July-Master List to be the same as the sample Workbook for (1) June-Master List.

    One VERY IMPORTANT thing to remember is to protect the Master Lists, so that nobody can insert or delete any Rows. That should solve your issue.

    Hope that helps.

    Regards.
    I only wish to add more rows for the boys and girls and then copy the same sheet till april and change the following names of each month... I believe this file is the only workable file that was already perfect.. well i guess till i had encountered this much number of students this year and probably next year too...

  77. #77
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: EXCEL - Cell Color Background (SHADING) using IF (statements)

    Hello wedzmer,

    It took many days to change everything as per your request. The attached revised Workbook now provides for 163 boys, and 163 girls. The large amount of formulas you have added to the far right of the sheet, were quite clumsy, and I have changed that for you as well. In the statistical summary information block at the bottom of the sheet, you have some formulas which I cannot arrive at a conclusion with, of exactly how you want to calculate what.

    Hope you are happy now.

    Regards.
    Attached Files Attached Files
    Last edited by Winon; 09-02-2016 at 03:21 AM. Reason: Corrected some Code

  78. #78
    Forum Contributor wedzmer's Avatar
    Join Date
    06-13-2014
    Location
    Philippines
    MS-Off Ver
    2016
    Posts
    422

    Re: EXCEL - Cell Color Background (SHADING) using IF (statements)

    Hello Sir Winon!

    I just logged in... A recent bombing incident took place in our city by terrorists group... The location was just few blocks away from our community... We're really vigilant nowadays...

    Thanks for working out on my request.. You're really god sent... I just finished testing it out and wow, it was way more than my expectations... Though how do I fix the printing? Everytime I try to click the update button, it cuts off and I've been trying to fix the margins on an A4 paper... It won't work even if I set the print area... Is there any difference with how you did it last time?

  79. #79
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: EXCEL - Cell Color Background (SHADING) using IF (statements)

    Hi wedzmer,

    Thanks for the feedback.

    A recent bombing incident took place in our city by terrorists group... The location was just few blocks away from our community... We're really vigilant nowadays...
    We suffer the same over here, although not maybe terrorist groups, but political race attacks. This country is in a terrible mess and we also have to be very alert and vigilant.

    Though how do I fix the printing? Every time I try to click the update button, it cuts off and I've been trying to fix the margins on an A4 paper... It won't work even if I set the print area... Is there any difference with how you did it last time?
    No, I did not do anything to the Code to effect Printing.

    Another important thing to remember is that, if empty rows are not hidden, before you do a Print Preview or Print, is to Click on the "Hide Rows" Button. If yo do not do that, the Print Preview or Print will include all empty Rows on the range.

    I have setup the print range, and page orientation to landscape, to meet with your printing needs.

    Please try the modified attached Workbook now.

    Take care,

    Regards.
    Attached Files Attached Files
    Last edited by Winon; 09-04-2016 at 06:25 PM. Reason: Forgot to include Workbook

  80. #80
    Forum Contributor wedzmer's Avatar
    Join Date
    06-13-2014
    Location
    Philippines
    MS-Off Ver
    2016
    Posts
    422

    Re: EXCEL - Cell Color Background (SHADING) using IF (statements)

    Hello sir! Thanks for the response... It took me awhile to check coz of the State of Emergency declared here in our country...

    Quote Originally Posted by Winon View Post
    Hi wedzmer,

    Thanks for the feedback.



    We suffer the same over here, although not maybe terrorist groups, but political race attacks. This country is in a terrible mess and we also have to be very alert and vigilant.
    .
    Where you actually from Sir?

    Quote Originally Posted by Winon View Post

    No, I did not do anything to the Code to effect Printing.
    It seems the file has a problem in hiding the last portion of the supposed page 2, the last row that counts the total attendance of girls and boys...

    And is there a way to detect which cell has a code that fetches other sheets? I mean, to know that this cell for example is fetching from a different file or a different sheet besides checking on cells one by one?

    Quote Originally Posted by Winon View Post

    I have setup the print range, and page orientation to landscape, to meet with your printing needs.

    Please try the modified attached Workbook now.

    Take care,

    Regards.
    Thanks a lot.. I really need to segregate each page for each section, page 1 for boys, page 2 for girls and page 3 for the summary sheet.

    God bless always sir!!!

  81. #81
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: EXCEL - Cell Color Background (SHADING) using IF (statements)

    Hello Teacher wedzmer,

    1. I am in South Africa. Old pensioner trying to survive on your equivalent of 7255 Peso per month. It is tough!

    2. That has been fixed in the revised attached sample Workbook.

    And is there a way to detect which cell has a code that fetches other sheets? I mean, to know that this cell for example is fetching from a different file or a different sheet besides checking on cells one by one?
    The only other way I know of is to Click on Data in the Ribbon, and then select Linked Data, it will display a list of any or all Workbooks, or Sheets, referencing the one you are working on. Select to one you want to check, and open it. Another way would be to click on a Cell until you find the formula with an external link. Then in the Formula Bar select only the part with External Link Name usually between [...] brackets, and copy it>Escape> HomeTab> Find> Paste in the Find What Box, and follow the steps from there.

    you may want to Click on the Star at the Bottom and to the left of my Post to Add Reputation.

    Regards.
    Attached Files Attached Files

  82. #82
    Forum Contributor wedzmer's Avatar
    Join Date
    06-13-2014
    Location
    Philippines
    MS-Off Ver
    2016
    Posts
    422

    Re: EXCEL - Cell Color Background (SHADING) using IF (statements)

    I am in South Africa. Old pensioner trying to survive on your equivalent of 7255 Peso per month. It is tough!
    Really? Someone just came to our School's Division Office and brought with himself an upgraded version of the templates I was trying to create, all with VBA/Macro coding.. He met up with my supervisors and proposed to sell his templates for 50,000.00 per school who'd use it. Roughly 350 schools are here in our division. Imagine how much money he'd get...

    I'm pretty sure your skills would have done a much better template...

    The only other way I know of is to Click on Data in the Ribbon, and then select Linked Data, it will display a list of any or all Workbooks, or Sheets, referencing the one you are working on. Select to one you want to check, and open it. Another way would be to click on a Cell until you find the formula with an external link. Then in the Formula Bar select only the part with External Link Name usually between [...] brackets, and copy it>Escape> HomeTab> Find> Paste in the Find What Box, and follow the steps from there.
    I'll try that later... I'll check the file first.. hehe

    you may want to Click on the Star at the Bottom and to the left of my Post to Add Reputation.
    You're the best forum guru here... ^_^

    Thanks for everything...

  83. #83
    Forum Contributor
    Join Date
    06-29-2016
    Location
    Bristol, England
    MS-Off Ver
    2007
    Posts
    355

    Re: EXCEL - Cell Color Background (SHADING) using IF (statements)

    All I can think of, very simple I hope, is to add extra columns with the required formats, then copy the data into them using an IF().

  84. #84
    Forum Contributor wedzmer's Avatar
    Join Date
    06-13-2014
    Location
    Philippines
    MS-Off Ver
    2016
    Posts
    422

    Re: EXCEL - Cell Color Background (SHADING) using IF (statements)

    Sir winon! Good day!

    I would like to ask what does this Run-Time Error 91 imply? I'm experiencing it while using the tempate you created above...

  85. #85
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: EXCEL - Cell Color Background (SHADING) using IF (statements)

    Hello wedzmer,

    Run-Time Error 91
    I cannot replicate your problem. Could you please tell me what it is that you do for this Error Msg to appear?

    Thank you.

  86. #86
    Forum Contributor wedzmer's Avatar
    Join Date
    06-13-2014
    Location
    Philippines
    MS-Off Ver
    2016
    Posts
    422

    Re: EXCEL - Cell Color Background (SHADING) using IF (statements)

    When I open the file you made and then I open the SF2 June file which is where the names of the automated template fetches the data, this Run-Time Error 91 pops up immediately.. and I'm not familiar about it at all.

  87. #87
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: EXCEL - Cell Color Background (SHADING) using IF (statements)

    and then I open the SF2 June file which is where the names of the automated template fetches the data
    I don't have such a file, could you please send my a sample of that file?

    thank you.

  88. #88
    Forum Contributor wedzmer's Avatar
    Join Date
    06-13-2014
    Location
    Philippines
    MS-Off Ver
    2016
    Posts
    422

    Re: EXCEL - Cell Color Background (SHADING) using IF (statements)

    i'm attaching the file now....

    the thing is, the template you made is actually the exact duplicate of the SF2 i attached, the biggest difference is, it's now macro based and automatically generating the shades for the absentee reports of the students...
    this is what we've been asked to fill-up monthly as an attendance report. and as you can see, the template is a 2003 excel based template, one of their reasons why they never accepted my proposal to make the School Forms into automatically generating forms...

    though I have one last problem with this and I hope you could check it out too...
    the template you made was pre-edited with some codes I've been using to fix some issues. for example:

    1. at the utmost right, we can see the REMARKS section and there is a data there in the SF2 which uses the codes "NLS, T/I, T/O, and LE".. these codes mean NO LONGER IN SCHOOL, TRANSFERRED-IN, TRANSFERRED OUT and LATE ENROLLEE respectively.
    these codes in the remarks section make a huge difference in the total for boys, girls and accumulative total of students since it indicates the status of the student.

    2. the problem i had starts with the coding which came from the main office who made the SF2, they are using different formats for the months in the remarks section depending on the code. Transferred-out students are using " - " to format the date as: 2016-06-15 while other codes use " / " as 2016/06/19
    i had problem with this since the fetching of the codes i was using needed to adjust between the "-" and "/". And i noticed that last year's coding was also different, they were using "/" and "-" alternately.

    3. If you would check the sum of boys or the sum of girls per day, the coding I used was somewhat incorrect (which I still don't know how to fix by the way). Since I need to adjust the date if a student was not yet enrolled during that day or during that month, and if the student was leaving school or out of the school already during that day or during that month. It really doesn't work at all.. I don't know why...

    I'm not sure if I'm making sense.. I do hope that I am... please help with this along with the Runtime Error 91 that pops up every now and then when I open the file...

    Thanks a lot and more power!

    By the way, I also uploaded the picture of the error code.. hehehe maybe it would help.. i dunno..runtime error 91.png
    Attached Files Attached Files

  89. #89
    Forum Contributor
    Join Date
    06-29-2016
    Location
    Bristol, England
    MS-Off Ver
    2007
    Posts
    355

    Re: EXCEL - Cell Color Background (SHADING) using IF (statements)

    Hi, for some reason your recent messages have been coming through to me on gmail. Not sure why

  90. #90
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: EXCEL - Cell Color Background (SHADING) using IF (statements)

    @Trebor777,

    Not sure why
    Check out Post# 83. That automatically subscribed you to this Thread. If you wish to no longer receive updates of this Thread then you may select Thread Tools and "Unsubscribe from this Thread". This is available just above any thread on a New Page.

    **********************

    Thank you for the feedback wedzmer, I shall get back to you as soon as I can.

    Regards

  91. #91
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: EXCEL - Cell Color Background (SHADING) using IF (statements)

    Hi wedzmer,

    The Workbook you have uploaded is faulty. It could be corrupt, have a virus, or some kind of serious problem, since it will only download in "Protected View", stripped clean of images, buttons, formulas, and Code.

    There is absolutely nothing I can do to help under these circumstances, SORRY!

    Regards.

  92. #92
    Forum Contributor wedzmer's Avatar
    Join Date
    06-13-2014
    Location
    Philippines
    MS-Off Ver
    2016
    Posts
    422

    Re: EXCEL - Cell Color Background (SHADING) using IF (statements)

    really sir winon?

    the file doesn't have any code at all, it's purely text, no image whatsoever.. huhu
    Last edited by wedzmer; 11-15-2016 at 01:20 PM.

  93. #93
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: EXCEL - Cell Color Background (SHADING) using IF (statements)

    Hi wedzmer,

    why don't you try to download your own file, and see if it works on your side?

  94. #94
    Forum Contributor wedzmer's Avatar
    Join Date
    06-13-2014
    Location
    Philippines
    MS-Off Ver
    2016
    Posts
    422

    Re: EXCEL - Cell Color Background (SHADING) using IF (statements)

    It does say that it opens on protected view.. actually, the file came from the registry of our department which is an online database.. all files that came from that database which are .xls files always sets a protected view when i open it since last 2012.

  95. #95
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: EXCEL - Cell Color Background (SHADING) using IF (statements)

    Hello wedzmer,

    all files that came from that database which are .xls files always sets a protected view when i open it since last 2012.
    Seems to me somebody has messed around with my code by "Automating the Shading of Cells", and stuffed it up in the process, hence the sudden Error Msg.!

    Try to save the file that came from the registry of your department, which is an online database, on an external drive, usb stick, also called a flash drive or different directory as an xlsm extension file, and let us try it that way, by sending me the file from a different Folder.

    Regards.

  96. #96
    Forum Contributor wedzmer's Avatar
    Join Date
    06-13-2014
    Location
    Philippines
    MS-Off Ver
    2016
    Posts
    422

    Re: EXCEL - Cell Color Background (SHADING) using IF (statements)

    Quote Originally Posted by Winon View Post
    Hello wedzmer,



    Seems to me somebody has messed around with my code by "Automating the Shading of Cells", and stuffed it up in the process, hence the sudden Error Msg.!

    Regards.

    Hello sir winon!

    Who could have the power to mess your codes? ������

    I'll download a new file from the department's online database and save it as an .xlsm later and post it here tonight... I'll just edit this post when I already have the file.

  97. #97
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: EXCEL - Cell Color Background (SHADING) using IF (statements)

    Hello wedzmer,

    In Post#88 you quote inter-Alia that;

    the thing is, the template you made is actually the exact duplicate of the SF2 i attached, the biggest difference is, it's now macro based and automatically generating the shades for the absentee reports of the students...
    this is what we've been asked to fill-up monthly as an attendance report. and as you can see, the template is a 2003 excel based template, one of their reasons why they never accepted my proposal to make the School Forms into automatically generating forms...
    Now my question is: Who automated the generating of the shades for the absentee reports of the students automatically...?

    Maybe that is the problem which causes the "Error Msg"

    Regards.

  98. #98
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: EXCEL - Cell Color Background (SHADING) using IF (statements)

    Post Duplicated somehow. Deleted.
    Last edited by Winon; 11-19-2016 at 11:46 AM.

  99. #99
    Forum Contributor wedzmer's Avatar
    Join Date
    06-13-2014
    Location
    Philippines
    MS-Off Ver
    2016
    Posts
    422

    Re: EXCEL - Cell Color Background (SHADING) using IF (statements)

    Hello sir winon!

    Quote Originally Posted by Winon View Post
    Hello wedzmer,

    Now my question is: Who automated the generating of the shades for the absentee reports of the students automatically...?

    Maybe that is the problem which causes the "Error Msg"

    Regards.
    I'm not really sure I understand your question... weren't you the one who made the SF2 into an automated generating absentee report form? I mean, you were the one who coded it right? So that the shades be converted into images representing the shades as indicated through the representation of tardiness and absences.

    Thanks for the help as always!

  100. #100
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: EXCEL - Cell Color Background (SHADING) using IF (statements)

    Hello wedzmer,

    I'm not really sure I understand your question...
    Sorry Sir, I misunderstood your statement - thanks for the clarification.



    1. at the utmost right, we can see the REMARKS section and there is a data there in the SF2 which uses the codes "NLS, T/I, T/O, and LE".. these codes mean NO LONGER IN SCHOOL, TRANSFERRED-IN, TRANSFERRED OUT and LATE ENROLLEE respectively.
    these codes in the remarks section make a huge difference in the total for boys, girls and accumulative total of students since it indicates the status of the student.
    I went through all of my records and cannot find any of the info as quoted above. So i can not work on that either.

    2. the problem i had starts with the coding which came from the main office who made the SF2, they are using different formats for the months in the remarks section depending on the code. Transferred-out students are using " - " to format the date as: 2016-06-15 while other codes use " / " as 2016/06/19
    i had problem with this since the fetching of the codes i was using needed to adjust between the "-" and "/". And i noticed that last year's coding was also different, they were using "/" and "-" alternately.

    3. If you would check the sum of boys or the sum of girls per day, the coding I used was somewhat incorrect (which I still don't know how to fix by the way). Since I need to adjust the date if a student was not yet enrolled during that day or during that month, and if the student was leaving school or out of the school already during that day or during that month. It really doesn't work at all.. I don't know why...

    I'm not sure if I'm making sense.. I do hope that I am... please help with this along with the Runtime Error 91 that pops up every now and then when I open the file...
    The Run-Time Error could quite possibly originate from your remarks above, and I would love to try and sort it out for you, if only I could get an unprotected copy of the Workbook in question.

    Kind regards.

  101. #101
    Forum Contributor wedzmer's Avatar
    Join Date
    06-13-2014
    Location
    Philippines
    MS-Off Ver
    2016
    Posts
    422

    Re: EXCEL - Cell Color Background (SHADING) using IF (statements)

    hello sir winon!

    I've tried to log-in on our online database to try to download a new file from the SF2 sheets. Unfortunately, the site is unavailable due to maintenance...

    Anyway, I tried to convert the file with a .xlsx format, i hope this one works..
    Attached Files Attached Files

  102. #102
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: EXCEL - Cell Color Background (SHADING) using IF (statements)

    Hello wedzmer,

    .xlsx format will not help.

    Please try .xlsm or .xlsb formats.

  103. #103
    Forum Contributor wedzmer's Avatar
    Join Date
    06-13-2014
    Location
    Philippines
    MS-Off Ver
    2016
    Posts
    422

    Re: EXCEL - Cell Color Background (SHADING) using IF (statements)

    hello sir winon!


    I've saved the file as an xlsb file.. I hope this one works..
    Attached Files Attached Files

  104. #104
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: EXCEL - Cell Color Background (SHADING) using IF (statements)

    Nope, no Go!

    Same as protected view without message of Protected View.

  105. #105
    Forum Contributor wedzmer's Avatar
    Join Date
    06-13-2014
    Location
    Philippines
    MS-Off Ver
    2016
    Posts
    422

    Re: EXCEL - Cell Color Background (SHADING) using IF (statements)

    Is there any way to fix this sir winon? ������

  106. #106
    Forum Contributor wedzmer's Avatar
    Join Date
    06-13-2014
    Location
    Philippines
    MS-Off Ver
    2016
    Posts
    422

    Re: EXCEL - Cell Color Background (SHADING) using IF (statements)

    Or could it be because I'm having problems with attaching it here...? Can I upload it from an external source sir, and save multiple files from xls, xlsx, xlsb and xlsm, then zip it all in one file? Just taking chances...

  107. #107
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: EXCEL - Cell Color Background (SHADING) using IF (statements)

    Hello Teacher wedzmer,

    Maybe we could do it this way round. In the Remarks Column I have highlighted Cells in Yellow which contains *NLS* etc.

    In one of the Protected Workbooks we have tried I saw you had something like view "Second Legend". If I am correct, please add it to the attached sample Workbook, as well as any other changes you may require.

    It is very important that you do not move or Delete any Columns or Rows, or Delete any Buttons as they are setup now.

    Once you are done you may send the Book back to me, and I shall see if I can fix things for you. You may also add another Sheet to the Workbook with clear explanations of your requirements.

    Regards.
    Attached Files Attached Files

  108. #108
    Forum Contributor wedzmer's Avatar
    Join Date
    06-13-2014
    Location
    Philippines
    MS-Off Ver
    2016
    Posts
    422

    Re: EXCEL - Cell Color Background (SHADING) using IF (statements)

    hello sir winon!

    I actually made use of these codes in two columns.. in column AE starting at cell AE13, i used these codes:

    Please Login or Register  to view this content.
    and this one is for column AJ starting at cell AJ13

    Please Login or Register  to view this content.
    the goal basically is to trace the student movement if they entered school or left school at such date... NLS and T/O basically garner the same result which is a student moving out of the school, LE and T/I means that the student entered the class at that specific date...

    I tested these codes and it does somehow garner the required result I was looking for, however, there's one problem, the format of the remarks should always have to be NLS DATE:YYYY/MM/DD where NLS could be any remark... i have a problem with this since I used a helping column which was to fetch the date from the said remarks section... the problem occurs when the date format changes from "/" to "-" and when it alters from YYYY/MM/DD to MM/DD/YYYY and the month and dates are below the value of 10.. excel somehow has a problem identifying which is the month and which is the day.. haha

    I'm such a noob!!!

  109. #109
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: EXCEL - Cell Color Background (SHADING) using IF (statements)

    Just browsing your SF2 (June) worksheet, column AC calculating the Absences, perhaps a formula like this could be used in the column instead of the countif formula....just a thought.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  110. #110
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: EXCEL - Cell Color Background (SHADING) using IF (statements)

    @ newdoverman,

    I truly appreciate your interest and comment in this Thread. If you click on the "Show Legend" Button, you will see just to the right of the Legend a List of abbreviations. The "C" is used to identify a full day absence, and that is what we need to extract. Hence the countif approach.

    Kind Regards.

  111. #111
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: EXCEL - Cell Color Background (SHADING) using IF (statements)

    Hi Teacher wedzmer,

    Thank you for the feedback. Would you please be so kind as to respond to the requests.

    1. In one of the Protected Workbooks we have tried I saw you had something like view "Second Legend". If I am correct, please add it to the attached sample Workbook, as well as any other changes you may require.

    2. Once you are done you may send the Book back to me, and I shall see if I can fix things for you. You may also add another Sheet to the Workbook with clear explanations of your requirements.
    Thank you.

  112. #112
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: EXCEL - Cell Color Background (SHADING) using IF (statements)

    The workbook that I perused had calculations that were counting A, B and C values that were on the row in total. That is why I suggested what I did. I see that the calculation has changed drastically in later books.

  113. #113
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: EXCEL - Cell Color Background (SHADING) using IF (statements)

    @ newdoverman,

    Have you now looked at the Sample Workbook in Post#107?

    I suspect the OP copied formulae from another workbook, creating invalid links. That I should be able to fix along with a bit of education how or why Excel does this, to prevent future occurrences of same.

  114. #114
    Forum Contributor wedzmer's Avatar
    Join Date
    06-13-2014
    Location
    Philippines
    MS-Off Ver
    2016
    Posts
    422

    Re: EXCEL - Cell Color Background (SHADING) using IF (statements)

    Quote Originally Posted by Winon View Post
    In one of the Protected Workbooks we have tried I saw you had something like view "Second Legend". If I am correct, please add it to the attached sample Workbook, as well as any other changes you may require.

    Once you are done you may send the Book back to me, and I shall see if I can fix things for you. You may also add another Sheet to the Workbook with clear explanations of your requirements.

    Regards.
    Hello sir winon,

    I've been looking for this one you've been referring to and I can't seem to find the workbook that has SECOND LEGEND...

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Change cell background color based on another cells background color
    By Queo in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 4
    Last Post: 06-10-2014, 05:28 AM
  2. Replies: 4
    Last Post: 12-29-2013, 11:41 PM
  3. Replies: 6
    Last Post: 11-07-2012, 10:46 AM
  4. Background Cell shading colors
    By Dean in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-02-2006, 10:20 AM
  5. Excel formulas based upon the color shading of a cell?
    By DGBG in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-12-2005, 06:06 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1