How JustAnswer Works:

  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.

Ask Richard Your Own Question

Richard
Richard, Software Engineer
Category: Microsoft Office
Satisfied Customers: 33882
Experience:  Over 15 year experience resolving Microsoft Office Issues
32989067
Type Your Microsoft Office Question Here...
Richard is online now

Hopefully you can assist me with this question on

Customer Question

Hello, Hopefully you can assist me with this question on Excel 2007, which may require VBA. I can confirm I have attached a workbook for demonstration purposes. I have unprotected the workbook and the VBA Project. I can confirm the worksheet named Skills
Matrix within the cell range C4:X48 has a number of cells which are shaded Green (U) with the use of conditional formatting. I require two conditions which will automatically change the Green (U) to Amber (L) or back to Green (U) should the requirements be
met at a different stage of the month. Condition 1: If the efficiency within the range on the KVI Efficiency Tracker worksheet drops below 90%, change U to L [Example cell CL6 is 86.25% on the KVI Efficiency Tracker worksheet, so the corresponding cell in
H6 in the Skills Matrix sheet should turn to L] Condition 2: If the % of errors in row 56 exceeds 1% within the Skills Analysis worksheet, change U to L [Example cell L56 is over 1% under Colleague 10 on the Skills Analysis worksheet, therefore the U’s within
Column L in the Skills Matrix sheet should turn to L] The one caveat is I’m unable to protect the cell range C4:X48 in the Skills Analysis worksheet since the flexibility to change an I or O still needs to be maintained. In respect to Just Answers I have had
a positive experience with a James F Griff. However, if another available member has the capability to address this question, then this will be fine. Any help, would be most appreciated. Rik
Submitted: 1 year ago.
Category: Microsoft Office
Customer: replied 1 year ago.
The follow up point I have attached the workbook as an .xls, since the original format as an .xlsb does not appear to be compatible with the Just Answers site.
Expert:  adamd-mod replied 1 year ago.
Hello there,
I've been working hard to find a Professional to assist you with your question, but sometimes finding the right Professional can take a little longer than expected.
I wonder whether you're ok with continuing to wait for an answer. If you are, please let me know and I will continue my search. If not, feel free to let me know and I will cancel this question for you.
Please note, I am just a moderator for this category, I can only try and find experts to help, I can't answer the question itself.
Thank you!
Adam
Customer: replied 1 year ago.
Hi Adam,Sure, I'm fine to wait. Simply put the Professional would require a strong knowledge of Excel 2007 and probably VBA.
Expert:  adamd-mod replied 1 year ago.
Hello,
We will continue to look for a Professional to assist you.
Thank you for your patience,
Adam
Expert:  Richard replied 1 year ago.
Thank you for your question, my name is ***** ***** I look forward to assisting you.
Please note, click on Reply to Expert to reply to me, a rating is only needed when we are finished.
Sorry about the delay you have experience.
Two questions please.
1)It will need to be a macro, so will itt be possible you press a button and this is all done? I can then code it to run on a button press and where should the button best be placed for you.
2) For condition to, it is the entire column that changes to L in that example correct?
Thank you
Customer: replied 1 year ago.
Hello,Thank you for your response. In respect to the initial question I would place the macro button on the Skills Matrix worksheet in the left hand corner cell range A1:A2.On the second question yes this is correct, where U’s are present at least. In this instance the quality is based upon the overall error rate and the efficiency is assessed against each individual process.Therefore, if the % of errors exceeds 1%, then all the U's turn L on the corresponding column.
The I's (Training), O's (Authorised by FLM) and blanks remain unchanged.However, if the % of errors is under 1%, then the changes would only be applied based upon the efficiency. Using the example of Colleague 6 process 3, 10 and 11 are below 90%, so the U’s on the three corresponding cells H6, H13 and H14 would turn to L.If the colleague's performance should change say at a later period and equals ‘both’ conditions, month efficiency equal or greater than 90% and the quality is under a 1% error rate then the L's revert back to U's.Hopefully this makes sense. Any further questions, let me know.Rik
Expert:  Richard replied 1 year ago.
Thanks Rik
I am actually based in the UK Timezone and not the US, so its the end of my day now (was just about to sign off).
If it is ok, I can work on this tomorrow for you please?
Customer: replied 1 year ago.
Sure, no problem. Thanks.
Expert:  Richard replied 1 year ago.
thanks and till then
Richard
Expert:  Richard replied 1 year ago.
ok, this is now done for you. Can you test it please and let me know the results
http://ge.tt/70ibK1M2/v/0?c
Thank you
Customer: replied 1 year ago.
Hello,Thanks for getting back to me. I have just tested the macro. In this instance one part of the macro worked fine.The efficiency under 90% did change to L and Colleague 10 (over 1%) did change from U to L.However, the efficiency over 90% also turned L. So for example cell CN17 on the KVI Efficiency Tracker sheet is currently 144.20%, which corresponds to cell J17 on the Skills Matrix worksheet has turned L. This should remain U.The “I” values under Colleague 12 in Red cells N17 and N19 also turned to an L.In respect to the quality under the column over 1% the intention was only U’s would turn to be L. Using the example of Colleague 10 cells L4, L6:L7, L23, L28, L30:L35. However, all the blank cells turned L along with these cells.Could you look into this when you get a free moment?Thanks, ***** *****Rik
Expert:  Richard replied 1 year ago.
Thanks Rik
Will look into and correct this first thing tomorrow morning for you.
Richard
Expert:  Richard replied 1 year ago.
Can you check it here please Rik
http://ge.tt/58hi74M2/v/0?c
Let me know the results please
Customer: replied 1 year ago.
Hello,I have outcome tested the efficiency and quality conditions. I can confirm it is closer albeit a few slight adjustments are required.The conditions tested between the U’s and L’s appeared fine.However, I applied a 91% value under Colleague 2 cell CH35 on the KVI Efficiency Tracker worksheet as a test. In this instance the “O” was changed to a “U”.I applied a 1.5% error rate value under Colleague 9 on the Skills Analysis sheet. In this instance after pressing the button the macro the "O" was overwritten with “L”, I then applied a couple of "I" as a subsequent test and these were also overwritten with "L".One of the key principles is the “O” and “I” is excluded, so in essence these remain unchanged whatever the efficiency or error rate maybe.Any help, would be most appreciated.Rik
Expert:  Richard replied 1 year ago.
ok, So you want O and I to always be left alone now?
That will require a bit of work to recode as it was not initially mentioned as a requirement so please let me know first.
Customer: replied 1 year ago.
Hello,Indeed. I apologise if I did not make it clear in the initial question. I probably clarified it in one of the other messages 10th August 09:55 on the third paragraph.I'm fine to wait a while for completion. Thanks, ***** *****Rik
Expert:  Richard replied 1 year ago.
You can download it from here Rik
http://ge.tt/1yFM37M2/v/0?c
Let me know the results please
Customer: replied 1 year ago.
Hello,Thank you for the prompt reply. However, in this instance the “O” and “I” still appear to be overwritten.Using the initial example as a test I changed cell H6 under colleague 6 to an "O" on the Skills Matrix worksheet [corresponding to CL6] which technically should exclude this from a change. However, it turned to “L” related to the efficiency under 90%.I then changed cell I5 to an “I” being the other exclusion which turned back to U after running the macro.In respect to the quality I changed cell N10 to “O” and changed the error rate to 1.2% which turned to “L. However, it is possible condition one was the trigger point.Any help, would be most appreciated.Rik
Expert:  Richard replied 1 year ago.
I see the issue, will work on it now
Expert:  Richard replied 1 year ago.
ok, tested as extensivly as I could.
The issue before was a reference bug which I fixed up.
I went through all the different conditions we have discussed and from what I test it now meets them
You can get it here
http://ge.tt/3j5ANBM2/v/0?c
Let me know if you have any difficulties please
Richard, Software Engineer
Category: Microsoft Office
Satisfied Customers: 33882
Experience: Over 15 year experience resolving Microsoft Office Issues
Richard and 5 other Microsoft Office Specialists are ready to help you
Customer: replied 1 year ago.
Hello,I can confirm I have tested this 7 or 8 times and it work's perfectly, great job. I will leave positive feedback.Thanks.Rik
Customer: replied 1 year ago.
Hello,I just performed another quick check on Monday morning and it turn’s out there was one slight issue.It appears the blanks have not been excluded, in reference to one of my earlier messages.I will provide another example I created three ‘new’ efficiency values in cells where U or L is not present:Colleague 1
CG6: 55%
CG7: 89%Colleague 12
CR4: 91%In this instance the two L’s and one U was populated, rather than remaining unchanged. This is key since there will be some processes where competence is not necessarily required.Could you look into this one if you get the opportunity? I’m guessing this would require an additional <> value in condition one.Thanks.Rik
Customer: replied 1 year ago.
Hello,It's fine. I worked it out, I added an extra <> "" related to the Skills Matrix worksheet under condition 1 on each cell row which appears to have resolved this.Thanks.Rik

What Customers are Saying:

 
 
 
  • My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed. One Happy Customer
< Previous | Next >
  • My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed. One Happy Customer
  • Wonderful service, prompt, efficient, and accurate. Couldn't have asked for more. I cannot thank you enough for your help. Mary C.
  • This expert is wonderful. They truly know what they are talking about, and they actually care about you. They really helped put my nerves at ease. Thank you so much!!!! Alex
  • Thank you for all your help. It is nice to know that this service is here for people like myself, who need answers fast and are not sure who to consult. GP
  • I couldn't be more satisfied! This is the site I will always come to when I need a second opinion. Justin
  • Just let me say that this encounter has been entirely professional and most helpful. I liked that I could ask additional questions and get answered in a very short turn around. Esther
  • Wonderful service, prompt, efficient, and accurate. Couldn't have asked for more. I cannot thank you enough for your help. Mary C.
 
 
 

Meet The Experts:

 
 
 
  • jstinehelfer

    jstinehelfer

    Information Systems Manager

    Satisfied Customers:

    23
    A+ Comptia Certified computer repair
< Previous | Next >
  • http://ww2.justanswer.com/uploads/JS/jstinehelfer/2012-6-6_215146_Pictureofme1.64x64.png jstinehelfer's Avatar

    jstinehelfer

    Information Systems Manager

    Satisfied Customers:

    23
    A+ Comptia Certified computer repair
  • http://ww2.justanswer.com/uploads/JA/JasonJames122/2011-12-16_135647_jasonjamesheadshotweb.64x64.jpg JasonJames122's Avatar

    JasonJames122

    Computer Enthusiast

    Satisfied Customers:

    0
    I manage all information systems (network of Windows PC's and servers with Microsoft Office products) for a small business
  • http://ww2.justanswer.com/uploads/JE/jessmagz/2012-6-6_18129_jm.64x64.jpg Jess M.'s Avatar

    Jess M.

    Computer Support Specialist

    Satisfied Customers:

    240
    Computer Software Specialist for more than 10 years
  • http://ww2.justanswer.com/uploads/JA/JACUSTOMERf8udkdxk/2013-8-3_15150_323738101505074393259301621172992o.64x64.jpg KamilAnwar1's Avatar

    KamilAnwar1

    Office Specialist

    Satisfied Customers:

    119
    8+ Years of Experience. / CCNA (S), CCNA (W), CCNA (RS), MCTS, MBCs.
  • http://ww2.justanswer.com/uploads/SE/Seashore2011/2011-12-6_33418_111205223015.64x64.jpg Chris L.'s Avatar

    Chris L.

    Support Specialist

    Satisfied Customers:

    111
    Certified Expert with over 10 years experience.
  • http://ww2.justanswer.com/uploads/WI/Windowspcfix/2013-8-19_153826_ja12.64x64.jpg James K.'s Avatar

    James K.

    Consultant

    Satisfied Customers:

    110
    Technical Director of IT Company
  • http://ww2.justanswer.com/uploads/KR/krkljatorm/2012-6-24_153417_ITMiro.64x64.jpg IT Miro's Avatar

    IT Miro

    Computer Scientist

    Satisfied Customers:

    56
    Bachelor's Degree in Information Technology, Microsoft Certified Professional
 
 
 

Related Microsoft Office Questions