How to add formula like area wise

R

Rohinikumar

I Hope some one can help for this formula that I have List of Vehicle details
with approved KM,
My subject is that drivers are running some kilometers but from my company
side we have some approved Kilometer list. Can I put formula as area wise I
mean if area name is TRD that approved KM was added in approve KM cell like
20. Pls find the below some list.

Vehicle No Startng KM Closing KM Total KM Area Aprrove KM
2417 1234 1263 29 TRD 20
1234 3258 3287 29 YUI 22
3214 9874 9903 29 OIU 28
9632 12358 12387 29 UJI 27
1478 3254 3283 29 STR 23
6547 2587 2616 29 KKR 24
9874 1456 1485 29 LLm 29
5821 65896 65925 29 LLO 25
6547 35469 35498 29 GTO 26
3214 14789 14818 29 FTO 24
6589 32145 32174 29 TOL 22
3215 69871 69900 29 KJS 21
 
F

FSt1

hi
i am having trouble understanding were the 20 came from. i have add the
number several way but can't seem to come up with 20. please provide more
details.

Regards
FSt1
 
R

Rohinikumar

Hi that 20 i have approved for them....as per reading KM 29 but as per our
agreement 20 only....i want to know if i mention some area name LIKE TRD how
can it will add 20 in the approved column
 
C

ck

Seems like you want to have the number 20 each time the area TRD appear and
so on.. If so, you will need to have a database of all the areas and have the
number beside it at the next column. Then set up a match and index formula
under the 'Approved KM' with the database.
 
C

ck

Assuming the following: Column A is the database of areas and column B is the
numbers that you want to reference. Column D is where your area is and E is
the place where you put the approve KM

A B C D E
1 TRD 20 TRD 20
2 YUI 22 YUI 22
3 OIU 28 UJI 27
4 UJI 27 YUI 22

In E1, paste this formula =INDEX($B$1:$B$4,MATCH(D1,$A$1:$A$4,0)) change the
necessary range and drag down.

Click the yes below if this help you
 
F

fisch4bill

Hi Rohinikumar,

You can do this dynamically using VBA code. The VBA method keeps you from
having to house additional data in the spreadsheet, and allows for any number
of rows being added to the sheets with no additional coding. The routine
assumes that you have titles in row 1, that columns A through E contain the
data for the Vehicles and their KM figures, etc. and automatically populates
column F with the appropriate allowed KM value for the Area entered in column
E. If your data is found in a different range, you'll need to adjust the code
accordingly.

Try the following:
Open the VBE by pressing ALT+F11
Press CTRL+R to display the Project Explorer pane if it is not visible
Double click on ThisWorkbook
In the lefthand dropdown select Workbook
In the righthand dropdown select SheetChange
Paste the following lines into the code window:

'Code starts here:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim isect As Variant
Set isect = Application.Intersect(Target, Range("E2:E" & Cells(Rows.Count,
"E").End(xlUp).Row))
With Target
Select Case .Value
Case "TRD"
Cells(.Row, .Column + 1).Value = 20
Case "KJS"
Cells(.Row, .Column + 1).Value = 21
Case "YUI", "TOL"
Cells(.Row, .Column + 1).Value = 22
Case "STR"
Cells(.Row, .Column + 1).Value = 23
Case "KKR", "FTO"
Cells(.Row, .Column + 1).Value = 24
Case "LLO"
Cells(.Row, .Column + 1).Value = 25
Case "GTO"
Cells(.Row, .Column + 1).Value = 26
Case "UJI"
Cells(.Row, .Column + 1).Value = 27
Case "OIU"
Cells(.Row, .Column + 1).Value = 28
Case "LLm"
Cells(.Row, .Column + 1).Value = 29
End Select
End With
End Sub
'Code ends here

Hope this helps
Bill
 
Top