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

Facing challenge to prioritize the work by category basis

uday

uday

Member
Sir,

I am working in one MNC company and my role is invoice processing. Every day we are extracting data from the Oracle database and create a dump of different kinds of invoices.

We copy the invoice number from the excel dump file and paste it into oracle to process. After processing we need to validate each and every processing action detail in column D (Status).


We need to complete our work by priority basis, like first we need to touch the P1 category after that we can touch P2 and P3.But no one is following this rule.


I have attached one shared excel file wherein work will be done by priority basis.

There are three priorities mentioned in column E i.e., P1, P2, P3. And column D contains different types of validation data.


The main task is we need to validate each and every row(Status-Column D) which is belongs to P1, after that we can touch the P2 and P3.But first we need to complete P1 by validating the status column D.

I don’t have any which is the best vba event or any logical formula to stop users to disobey the rule.i.e, validate the P1 category first.

Please help…….
 

Attachments

  • Sample.xlsx
    9.5 KB · Views: 4
U r genius ....;)
Ok. can I hide the P2 and P3 data until the P1 is done...or restrict the users to copy invoice numbers(Column B) from the less prioritize line.(P2 and P3) ?

Please note :
The main file will be in shared format and handling by multiusers.
 
Last edited:
Hi Uday ,

I would suggest that instead of hiding the P2 and P3 rows , why not lock them , so that users know they are present but not available for data entry ? As and when P1 is completed , P2 can be unlocked , and as and when P1 and P2 are completed , P3 can be unlocked.

Narayan
 
Good idea...but Narayan one more thing hovering on my mind...suppose users are falsely validating the p1 rows to see or unlock the p2 and p3 then all the effort will be vanish.So I have came across with one idea-After completing the p1 rows if any email automatically trigger to supervisor mail box for further request or unlocking the p2 or and p3.then supervisor can do the random sanity check of one or two invoice number and unlock the p2 and p3 rows.
You can use my email id(uroy67640@gmail.com)in vba coding string for testing.
What do you think is this idea would work or not?
 
Hi Uday ,

I am not in favour of such 'protection' mechanisms ; if you suspect your employees or colleagues of such activity , then they are probably better off outside the company than in.

Any protection mechanism , in my opinion , should be to prevent human errors , and not fraudulent activity , especially with software such as Excel. If you want tighter security , go in for professional software , which has all such safeguards built in.

Narayan
 
Thanks for your help once again.but when I am trying to use this same code with different template its not working..The only differences are column name is change to E for status and I for priority.and the range increase to 1000 for each column name.I have attached the new template .You can find the coding in the MAIN sheet.

Please help Narayan...
 

Attachments

  • complete beforeclose project (1).xls
    317.5 KB · Views: 1
Thanks its working perfectly ...Narayan..But can u please tell me why u have changed the coding string from Target.Offset(, 1) to Target.Offset(, 4) and add If Target.Count > 1 Then Exit Sub,+ Firstrow - 2 these two extra lines into coding string.I want to know the logic behind it and learn from u.Please reply
 
Hi Uday ,

The change in the OFFSET parameter is to ensure that the correct column is being referenced ; earlier the target column , where data was being entered was column D ; the priority values P1 , P2 and P3 were in column E , which is one column away from column D. Now , the target column , where data is being entered is column E , and the priority values are to be found in column I , which is 4 columns away from column E.

The next change viz. the introduction of the statement :

If Target.Count > 1 Then Exit Sub

is to take care of the situation when you have entered data and realised it is a mistake and press the DEL key to erase the cell contents. Normally data entry can only be done one cell at a time , since every time you change a cell , that event is trapped and taken care of. However , when you are clearing cells , you can select a range of cells and press the DEL key ; including this statement ensures that this event is ignored.

The inclusion of the +Firstrow - 2 part in the calculation of the value for Lastrow is because the MATCH statement always returns a value from 1 onwards ; suppose we have 10 values of P1 , starting from cell I7. This means that P2 , if it is present , will start from row 17. Now , suppose there are 7 values of P2 ; P3 , if it is present , will therefore start from row 24.

When we have the first MATCH statement , which is :

=MATCH(TRUE,$I$7:$I$1000<>"P1",0)

the first cell in column I where the value is not equal to P1 will be in row 17 , and the value returned by the MATCH formula will be 11 ; now , when we are displaying the message that all entries in the P1 range are to be filled up , the address that is to be displayed is $I$7:$I$16 ; this is obtained by :

11 + Firstrow - 2

where Firstrow equals 7.

Similarly , when we use the next MATCH statement , which is :

=MATCH("P3",$I$7:$I$1000,0)

the first cell in column I where the value is equal to P3 will be in row 24 , and the value returned by the MATCH formula will be 18 ; now , when we are displaying the message that all entries in the P1 range are to be filled up , the address that is to be displayed is $I$7:$I$23 ; this is obtained by :

18 + Firstrow - 2

Narayan
 
Narayan..I do not want to calculate the week off day's (sat and sun)in column L.Is there any formula to calculate the week off day..Please help
 
Hi Uday,

If I understood it right, you want to calculate Week off days (Sat & sun) between Column J & Column K dates. If so, you can try below formula to calculate it,

=SUMPRODUCT((WEEKDAY(ROW(INDIRECT(J7&":"&K7)),2)=6)+(WEEKDAY(ROW(INDIRECT(J7&":"&K7)),2)=7))

Regards,
 
Thanks....its working ... I need one more help...in (column I),I have put some formula like this =IF(L7>2,"P1",IF(L7<=2,"P2",IF(L7=0,"P3"))) but its not working and some its showing FALSE value....My target is if column L cell value>2 then priority should be P1, if cell value is <2 then P2 AND if cell value =0 then priority would change to p3...Please help.
 
Sorry ..I have tried your weekday formula...actually its not working....I was wrong..If I change the scan date to 13 mar2014 still in the difference column showing cell value 2 if I change it to 12 mar still it remain same.If I count from Thursday and wed without sat and sun then the right count will be 4 and 5 in column L.
I need difference in between scandate and current date without the count of sat,sun.Please help
 
Yes,, I have checked the Priority formula is working in column I..I need help for the weekday formula.
 
Can you please tell me the logic behind this(=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(J7&":"&K7)),2)<6))) formula?I want to learn.
 
Excel treats dates as Serial Numbers. So Basically this formula uses INDIRECT function to generate a series of numbers from Scan date to current date, and since there is a concatenation with : in INDIRECT formula its act like a refrence in string fromat, Something Like "41000:41110", Now ROW function will simply return ROW number 41000:41110. Than these are simple numbers which are tested for their weekday through WEEKDAY formula, Since I had selected Return type of WEEKDAY function as 2 (i.e. Monday is 1 and SUNDAY is 7) so any date whose weekday is less than 6 i.e. MON, TUE, WED, THU, FRI will return a TRUE and this give us an array of TRUE and FALSE which I converted to 1 & 0 by --.

So finally an array of 1 & 0 goes to SUMPRODUCT function and it returns count of WORKDAYs.

Just advise if you need more clarification on this.

Regards,
 
Thanks for ur reply....I have many questions.Those are like below
1) Why should you concatenate two cell value with the symbol ":"?Is it necessary,because if I m trying to get a difference in between two cell value i.e j7-k7..I do not need to add : symbol.

2)Is there any specific return type for weekday functions.?As you mentioned u have selected function 2.What kind of format stands for function 1 or other number type.?

Please reply
 
Hi Uday ,

The word concatenate is not the issue ; the colon symbol is for specifying a range of cells , just as we use A1:A30 , $1:$1 , and so on.

The basic function is the ROW function ; when you use the ROW function as in :

=ROW($1:$5)

you can see that this returns a 5 x 1 array of numbers {1;2;3;4;5}.

=COLUMN($A:$E)

will return a 1 x 5 array of numbers {1,2,3,4,5}

Unfortunately , the ROW function will only accept literals i.e. numbers in this case. You cannot use the ROW function in :

=ROW(C5:C6)

where C5 contains 1 , and C6 contains 5.

=ROW(C5:C6)

will return the 2 x 1 array {5;6}.

To use the ROW function with references , you need to use one of two methods , one using INDEX , and the other using INDIRECT.

Some additional information is posted here :

http://chandoo.org/forum/threads/working-days-and-hours-only.12185/#post-71453

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