• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need VBA help for a quality tracker

A

Alpana

New Member
Hi,


I am working on quality tracker which would helps us give details about the performance of an individual.The one we are using now is an manual entry as it involves data from several spreadsheets. Right now, I am working on an inbuilt tracker ,where giving the necessary field it would give me the desired result.I have pasted my sample files in the below link.


The result I am looking is :


- I have two files , one is tracker and other one is project file(1234.xls).

- When I enter a field in the tracker , it should vlookup the field in the project file and highlight the necessary fields.

- The complete criteria with an example is explained in the Tracker excel.

- Note: The project file is located in a different folder and tracker is in a different folder and Project file is closed.


https://skydrive.live.com/redir?resid=662A3C1E79A372BB!120&authkey=!ACnFdGDOUYVGf28


I am not sure if this works, but wanted to give it a try.Since, many of my VBA queries have been resolved from this forum.Hope I get a solution for this.


Let me know if you need any more information.


Thanks in advance.


Regards,

Alpana.
 
Hi Alpana,


I am not able to download your file from the location. Can you please upload it here?


speedyshare.com/


Kaushik
 
Hi Kaushik,


Please find the link below , i have uploaded the files again.


http://speedy.sh/nJgfr/QC-tracker.rar


Alpana
 
Hi Alpana,


It is coming as a exe.rar file and I am not able to see it...I do not know if others are facing same problem or it is happening to me only.


Can I have this file here?


pharmacyjukaushik@yahoo.com


Sorry for the inconvenience caused.


Kaushik
 
Hi,


No worries,i have sent you the files to the emaild.


I have reloaded the files in the below links.


http://speedy.sh/rYEMu/1234.xls


http://speedy.sh/3aMHQ/Tracker.xls


And thank you very much for the help.


Alpana.
 
Dear Alpana,


Now I am able to download the file.


Let me explain my understanding to you before we get into the coding. Based on user’s input, code will open Tracker workbook, copy/lookup the contents (per criteria), do some highlighting activities(as specified), save and and close the tracker workbook. The copied/look up contents should be pasted/placed in 1234 workbook.


But I have couple of doubts, can u plz clarify these?


1 ) Will the lookup happen for task type and project ID? Is there any chance that the project ID may vary?


2) Where user will provide input? Somewhere in 1234 workbook and accordingly lookup should happen from tracker workbook? Plz specify.


3) In tracker workbook, I could see taskA is entered for three times. So for task A and project ID 1234, do you want to lookup/fetch all the entries/rows from Col C and D?


3)Regarding criteria2, do you actually want columns to be highlighted and respective rows?


Looking forward to your reply.


Kaushik
 
Hi Kaushik,


I am sorry.I guess the understanding is vice versa from the output i am looking.I will expalin you step wise procedure below.


File description:


1234.xls - This is the project file where the details of tasks are captured , for example Task A is planned by Ravi and worked by Arun, so in this scenario i have to capture the quality of Ravi and Arun separetely based on the criteria.


Tracker.xls - This is the tracker file,where quality of ravi and arun and different individuals is tracked based on who has planned and who has worked.


- For example, I enter Task A in column A2 and project 1234 in column B2, then the code should open 1234.xls and look up based on task and give out the inputs of who has planned and worked for that particular task from the project file to tracker file.


- Here, Always the planned and worked might not be same in the project file, so when it lookup's the data into tracker sheet , the columns from D to I should highlight as belwo.


a) If planned and worked columns are same , i.e, both are under Ravi name, then columns D to I should be highlighted/open for editing.


b) If planned and worked columns are differnet.i.e, Ravi and arun , then it should give me two outputs.

Ravi has planned - So first column should highlight/open for editing only column D & E , where as G, H, I are locked.

Arun has worked - So an entry has to be created in the second column where ccolums G, H & I are open for editing.


In this way, i am tracking for both ravi and arun based on their criteria of work(Planned/Worked) from project sheet(1234.xls)


Hope this gives an overview of what i am looking at.Let me know if you need any more information.


Alpana.
 
Hi Alpana,


Read your post...understood the points...I just quickly worked on the first part of your work. Below here are the two files. Can you plz check and let us know if this is what you are looking for as the initial part of your work?


If this is fine, we will then work on second part (highlighting and locking columns)of your request.


http://speedy.sh/68TzN/Tracker.xls


http://speedy.sh/68TxN/1234.xls


Notes:


1) Save the files in your desktop.(later you can change the path of your project file(1234.xls) and pass the same accordingly to the code)


2)Open the tracker file (keep 1234.xls file closed), enter the task name in col A and hit the blue button to check if the data are being entered correctly.(as of now, taskA is enetered, so you can directly hit the button to get the names).


Regards,

Kaushik
 
Hi Kaushik,


Thank you very much , it helps , but having some difficulties as given below:


1) It gives data for one task at a time, and if I enter around 4 task together and click the button , it gives me data only for the last one,leaving the first three blank.


2) Once the entry is looked up , the tracker sheet is closed and the file 1234.xls is open.I have to reopen the tracker sheet to check changes.


Additionally, is it possible to link around 2 or 3 project files and enter different tasks ( This we can look in the last once this is achieved).


Once again,thanks a lott for helping me in getting this one together.


Regards,

Alpana.
 
Hi Alpana,


First thing: I did not realize that all the entries need to be looked up at once. So in that case, we need to run a loop for all the entries. Incorporated the modification in the code.


Second thing: It was so silly of me writing the code in such a way that it closes the tracker workbook instead of 1234.xls workbook. I have modified it , now it will close 1234.xls workbook and will keep tracker workbook open.


Please check it and let me know if it is fine.


http://speedy.sh/MDyaK/Tracker.xls


Regards,

Kaushik
 
Hi Kaushik,


No worries,it happens ...:)


And coming to code, its working fine.successful in the first part.


Alpana..
 
Ok that's great..tomorrow I will be little bsy...so expect a little delay from my side on second part...hope it's fine with you...


Anyways...happy deewali to you and your family..


Regards,

Kaushik
 
Hey thats not a problem, will wait..and wish you a happy and safe diwali to you and your family.


Enjoy..


Regards,

Alpana.
 
Good morning Alpana,


Just started working on the second part of your problem but stuck in the middle because of the doubts mentioned below:


1) You mentioned columns to be highlighted based on the below criteria:

i) If both names are same then columns D to I should be highlighted/open for editing.


I don't understand why column need to be highlighted! Should not it be respective rows?


For e.g., C2 = D2 (both are Ravi). Then do you want Col G to I should be open for editing (should not be locked) or G2 to I2 should be open for editing? The reason I am asking this question is: In the next cell it might happen that C3=D3, in that case your next rule (mentioned below) should be applied for locking/unlocking activities.


ii) If both the names are different,then you want two outputs as follows:


For e.g., C2 (Ravi) <> D2(Arun)...then according to you:


a)First column should highlight/open for editing only column D & E , where as G, H, I are locked.

So you mean to say, only Col D and E should be unlocked while col G,H, I should be locked.


My question is here again, should it be columns or rows(G2:I2)?


b)You have also said, an entry has to be created in the second column where columns G, H & I are open for editing. So you mean to say, Col G,H,I should be unlocked while D& E should be locked.(My question regarding rows are again same as above)


Point number "b" is completely an opposite instruction of point number "a". I do not understand how both these criteria are possible to implement in same cell while names are different. Probably I do not understand your requirement clearly here. Please clarify.


One more question: You have not mentioned anything about Column F(To be locked/unlocked). What about this?


Thank you for your understanding and cooperation.


Regards,

Kaushik
 
Hi Kaushik,


First of all thanks a lott for attending my request so patiently, truly appreciate it.


and sorry for my confusion around columns and rows.


Answer:


1) You mentioned columns to be highlighted based on the below criteria:


i) If both names are same then rows E to I should be highlighted/open for editing.


For e.g., C2 = D2 (both are Ravi). Then the rows from E2 to I2 should be open for editing ( entering Yes/No).


ii) If both the names are different,then need two outputs as follows:


For e.g., C2 (Ravi) <> D2(Arun)...then:


a)column E2 & F2 should be open for editing ( as this is the criteria to validate Ravi's work who has planned for that particular task) and G2, H2, I2 are locked( I shouldnt be entering anything in this).


So you mean to say, only rows E2 to F2 should be unlocked while rows G2,H2, I2 should be locked - Yes .


b)You have also said, an entry has to be created in the second row where rows G3, H3 & I3 are open for editing. So you mean to say, rows G3,H3,I3 should be unlocked while E3 & F3 should be locked.


Point number "b" is completely an opposite instruction of point number "a". I do not understand how both these criteria are possible to implement in same cell while names are different. Probably I do not understand your requirement clearly here. Please clarify - When the names are different , i need to track for the person in Planned as well as in Worked,based on the classification in two different rows.( i.e, For PLANNED the rows - First time right in understanding/Planning are the criteria and for the WORKED the rows Floor plan update/Other errors/Deadlines discussed and extended are the criteria)


One more question: You have not mentioned anything about Column F(To be locked/unlocked). What about this? - Sorry This was an typo error where instead of F , I have started the cell identification from D.


Hope now, it clarifies.If it does not then I can share an sample file (created manually) once I am back home.


Regards,

Alpana.
 
You must log in or register to reply here.
Back
Top