If Condition

  • Thread starter Thread starter nhims786
  • Start date Start date
N

nhims786

I have got the following table in a Excel worksheet:

1 A B C D E
2 Name Grade Fee1 Fee2 Fee3
3 James I 1000 1500 2000
4 Peter II 1500 2000 2500
5 Kate III 2000 2500 3000


Actually the data in the cells C3-E3, C4-E4 & C5-E5 don't exist. What I want is when I enter 'I' in the cell B2, using IF (or any other) condition, the cells in C3, D3 & E3 should automatically be filled with 1000, 1500 & 2000 respectively. Same is the case with the data in the cell B4 i.e. when I enter 'II' in the cell B4, using IF (or any other) condition, the cells in C4, D4 & E4 should automatically be filled with 1500, 2000 & 2500 respectively.

Is this possible in MS-Excel 2007?

Thanks
 
nhims786 said:
I have got the following table in a Excel worksheet:

1 A B C D E
2 Name Grade Fee1 Fee2 Fee3
3 James I 1000 1500 2000
4 Peter II 1500 2000 2500
5 Kate III 2000 2500 3000


Actually the data in the cells C3-E3, C4-E4 & C5-E5 don't exist. What I
want is when I enter 'I' in the cell B2, using IF (or any other)
condition, the cells in C3, D3 & E3 should automatically be filled with
1000, 1500 & 2000 respectively. Same is the case with the data in the
cell B4 i.e. when I enter 'II' in the cell B4, using IF (or any other)
condition, the cells in C4, D4 & E4 should automatically be filled with
1500, 2000 & 2500 respectively.

Is this possible in MS-Excel 2007?
Click to expand...

I do a vaguely similar thing for my own spreadsheets. Put this in the
sheet's object in the VBA editor:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range
For Each cell In Target
If 2 = cell.Column Then
ro = cell.Row
Select Case cell.Value
Case "I"
Cells(ro, 3).Value = 1000
Cells(ro, 4).Value = 1500
Cells(ro, 5).Value = 2000
Case "II"
Cells(ro, 3).Value = 1500
Cells(ro, 4).Value = 2000
Cells(ro, 5).Value = 2500
Case "III"
Cells(ro, 3).Value = 2000
Cells(ro, 4).Value = 2500
Cells(ro, 5).Value = 3000
Case "IV"
Cells(ro, 3).Value = 2500
Cells(ro, 4).Value = 3000
Cells(ro, 5).Value = 3500
End Select
End If
Next
End Sub

If you need this to work on all sheets in the workbook, put it in the
ThisWorkbook object instead, and change the first line to this:

Private Sub Workbook_SheetChange(ByVal Sh As Object, _
ByVal Target As Range)

Alternately, you can do this without macros. Paste this into C3:

=IF($B3="I",1000,IF($B3="II",1500,IF($B3="III",2000,IF($B3="IV",2500,""))))

....and D3:
=IF($B3="I",1500,IF($B3="II",2000,IF($B3="III",2500,IF($B3="IV",3000,""))))

....and E3:
=IF($B3="I",2000,IF($B3="II",2500,IF($B3="III",3000,IF($B3="IV",3500,""))))

....and then copy down. If you have more than 4 "grades", just add another
nested IF() for each one.
 
Thank you Sir for your response. Its working fine with a single sheet but when I changed it to to the whole workbook by replacing

Private Sub Worksheet_Change(ByVal Target As Range)

with

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

its not working for the other sheets in the workbook. Do I need to make any other changes in the function to make it work?
 
Hi,

Am Tue, 25 Feb 2014 20:43:21 -0800 (PST) schrieb (e-mail address removed):
1 A B C D E
2 Name Grade Fee1 Fee2 Fee3
3 James I 1000 1500 2000
4 Peter II 1500 2000 2500
5 Kate III 2000 2500 3000
Click to expand...

another suggestion:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("B:B")) Is Nothing Or _
Target.Count > 1 Then Exit Sub

Dim valStart As Double

Select Case Target
Case "I"
valStart = 1000
Case "II"
valStart = 1500
Case "III"
valStart = 2000
End Select
Target.Offset(, 1) = valStart
Target.Offset(, 2) = valStart + 500
Target.Offset(, 3) = valStart + 1000

End Sub


Regards
Claus B.
 
Back
Top