[prempkn]

Dear Techsupportguy,

In the attached excel file there is "Priority" And "Validation" Sheet's......

In the "Priority sheet" "M" Column we need to apply priority like 1,2,3,4... up to 9.....
In the "Validation" sheet there is rules how to apply priority level....

Eg : If "Amount (USD)" in "L" Column contains Above 50000 USD and "Ageing vs. WF tool" in Column "H" contains 30days and above that has to be priority level 1. likewise we need to apply priority level for all numbers based on the rules given in the Validation sheet........

Please help me with coding to apply priority level's in one click......

Thanks
Prem

[etaf]

you should be able to do this with an embedded IF statement

just want to check the validation table

anything <= 10days = level 9
>10 days and any workflow usd = will be level 8
>15 days and if workflows usd >10,000 = level 5
>30 days and if workflows usd anything under or = 10,000 = level 7
>30 days and if workflows usd >10,000 and <= 25,000 = level 3
>30 days and if workflows usd >25,000 and <= 50,000 = level 2
>30 days and if workflows usd >50,000 = level 1
>50 days is level 6 - but I assume you dont want to alter the USD - so thats actually
>50 days and if workflows usd <=10,000 = level 6
>70 days and if workflows usd <=10,000 = level 4

so I would almost write that IF statement

i'll try out and post back - but maybe tomorrow - so someone else may answer

would you confirm the above though

if so then I think this is the order the IF statement needs to be written to ensure the levels are not overwritten
>30 days and if workflows usd >50,000 = level 1
>30 days and if workflows usd >25,000 and <= 50,000 = level 2
>30 days and if workflows usd >10,000 and <= 25,000 = level 3
>70 days and if workflows usd <=10,000 = level 4
>50 days and if workflows usd <=10,000 = level 6
>30 days and if workflows usd <= 10,000 = level 7
>15 days and if workflows usd >10,000 = level 5
>10 days and any workflow usd = will be level 8
anything <= 10days = level 9
would you check the logic flow - and assume you start at the top and work down and that any changes in levels are correct

clearly the logic above is not the same as you have used in the spreadsheet
i used this formula and in the first few cells I got a level 5 - where you had a 1 or n/a

=IF(AND(H2>30,L2>50000),1,IF(AND(H2>30,L2>25000),2,IF(AND(H2>30,L2>10000),3,IF(H2>70,4,IF(H2>50,6,IF(H2>30,7,IF(AND(H2>15,L2>10000),5,IF(H2>10,8,9))))))))

so i have misunderstood the requirement completely

in rows 2, 3 and 4

M2 = level 1
you have H2 = 18 L2 = 686,647 - so not a level 1 as its less than 30days
but greater than 15 days and 10,000 = level 5
M3 = 1
you have H3= 18 L3 = 561500 - so not level 1 as its less than 30days
but greater than 15 days and 10,000 = level 5
M4 = N/A
you have H4 = 30 and L4 =304,311
so greater than 15days and greater than 10000 = level 5

[20_2_Many]

I have the same misunderstanding.
Your 'Validation' rules seem to override other parameters of your validation because you use ALL as part of the requirement. ( ' ALL over 49 days = Level 6 or ' ALL over 69 days = Level 4)
M26 is Level 4 but under 15 days so ALL amounts and ALL days make it Level 9 and Level 4 is + 70 days

Here is some code that might help with your better understanding of the requirements

Option Explicit
Option Compare Text

Dim LastRow, I, x, y, PL As Integer

Sub SetPriority()
Sheets("Priority WF").Select
LastRow = Range("A65536").End(xlUp).Row
For I = 2 To LastRow
PL = 9
Range("M" & I).Select
ActiveCell = 9
x = ActiveCell.Offset(0, -1)
y = ActiveCell.Offset(0, -5)
Select Case y
Case Is > 69
ActiveCell = 4
If x >= 10000 Then ActiveCell = 3
If x >= 25000 Then ActiveCell = 2
If x >= 50000 Then ActiveCell = 1
Case 50 To 69
ActiveCell = 6
If x >= 10000 Then ActiveCell = 3
If x >= 25000 Then ActiveCell = 2
If x >= 50000 Then ActiveCell = 1
Case 30 To 49
ActiveCell = 7
If x >= 10000 Then ActiveCell = 3
If x >= 25000 Then ActiveCell = 2
If x >= 50000 Then ActiveCell = 1
Case 15 To 29
ActiveCell = 5
If x >= 10000 Then ActiveCell = 3
If x >= 25000 Then ActiveCell = 2
If x >= 50000 Then ActiveCell = 1
Case 10 To 14
ActiveCell = 8
If x >= 10000 Then ActiveCell = 3
If x >= 25000 Then ActiveCell = 2
If x >= 50000 Then ActiveCell = 1
End Select
Next I
USDAging
End Sub

Sub USDAging()
ActiveWorkbook.Worksheets("Priority WF").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Priority WF").Sort.SortFields.Add Key:=Range( _
"L2:L1675"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Priority WF").Sort.SortFields.Add Key:=Range( _
"H2:H1675"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Priority WF").Sort
.SetRange Range("A1:S1675")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub

[Garf13LD]

same as etf's logic except i treat +??days as >=??days