Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
If Condition
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
If Condition
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
If Condition
OK....OK....I got it.....thanks a lot
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
If Condition
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
if condition | Excel Discussion (Misc queries) | |||
lookup with multiple condition, but one condition to satisfy is en | Excel Worksheet Functions | |||
Combine an OR condition with an AND condition | Excel Discussion (Misc queries) | |||
Condition 1 overules condition 2? | Excel Worksheet Functions | |||
I need 4 condition for condition formatting | Excel Programming |