ExcelBanter - If Condition

nhims786 wrote:

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?

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",200 0,IF($B3="IV",2500,""))))

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

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

....and then copy down. If you have more than 4 "grades", just add another
nested IF() for each one.

--
They told us not to wish in the first place, not to aspire, not to try;
to be quiet, to play nice, to shoot low and aspire not at all.
They are always wrong. Follow your dreams. Make your wishes.
Create the future. And above all, believe in yourself.