Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Attaching formula to cells
If Sheets(“Sheet1”).Range(“L1”) 0 Or Sheets(“Sheet1”).Range(“L1”) < 5 Then Sheets(“Sheet1”).Range(“M1”) = 4 :End If the above is 4 or < 20 Then M1 = 5 If the above is 19 or < 120 Then M1 = 6 If the above is 119 Them M1 = 7 The above are the conditions that I want to set so that the value that appears in column M is dependant on the value that is entered in column L. How do I write the formula? How do I attach it to all the rows in column M? I need this because the value that appears in column M will be used as the column number that I will use in a VLOOKUP table. -- grahammal ------------------------------------------------------------------------ grahammal's Profile: http://www.excelforum.com/member.php...o&userid=20336 View this thread: http://www.excelforum.com/showthread...hreadid=520529 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Attaching formula to cells
In M1, enter formula:
=VLOOKUP(L1,{0,4;5,5;20,6;120,7},2,1) That will automatically calculate M1 HTH -- AP "grahammal" a écrit dans le message de ... If Sheets("Sheet1").Range("L1") 0 Or Sheets("Sheet1").Range("L1") < 5 Then Sheets("Sheet1").Range("M1") = 4 :End If the above is 4 or < 20 Then M1 = 5 If the above is 19 or < 120 Then M1 = 6 If the above is 119 Them M1 = 7 The above are the conditions that I want to set so that the value that appears in column M is dependant on the value that is entered in column L. How do I write the formula? How do I attach it to all the rows in column M? I need this because the value that appears in column M will be used as the column number that I will use in a VLOOKUP table. -- grahammal ------------------------------------------------------------------------ grahammal's Profile: http://www.excelforum.com/member.php...o&userid=20336 View this thread: http://www.excelforum.com/showthread...hreadid=520529 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Attaching formula to cells
=VLOOKUP(L10,{0,4;5,5;20,6;120,7},2,1) The above generates a 4,5,6 or 7 and is exactly what I wanted. Next problem:- Where the above generates the required number, I need to incorperate that in the VLOOKUP table below. =VLOOKUP(F10,'Mobile TRG Rate Card'!$A$2:$G$18,6,FALSE)*L10 It needs to take the place of the 6 entry which is the data column to look at. How can I insert the generated number into the table above. -- grahammal ------------------------------------------------------------------------ grahammal's Profile: http://www.excelforum.com/member.php...o&userid=20336 View this thread: http://www.excelforum.com/showthread...hreadid=520529 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Attaching formula to cells
Your first message said:
'--------------------------------- Then Sheets("Sheet1").Range("M1") = 4 :End If the above is 4 or < 20 Then M1 = 5 If the above is 19 or < 120 Then M1 = 6 If the above is 119 Them M1 = 7 '------------------------------------ Now you don't want 4, 5 6 or 7 in M1 ?!?!?! -- AP "grahammal" a écrit dans le message de ... =VLOOKUP(L10,{0,4;5,5;20,6;120,7},2,1) The above generates a 4,5,6 or 7 and is exactly what I wanted. Next problem:- Where the above generates the required number, I need to incorperate that in the VLOOKUP table below. =VLOOKUP(F10,'Mobile TRG Rate Card'!$A$2:$G$18,6,FALSE)*L10 It needs to take the place of the 6 entry which is the data column to look at. How can I insert the generated number into the table above. -- grahammal ------------------------------------------------------------------------ grahammal's Profile: http://www.excelforum.com/member.php...o&userid=20336 View this thread: http://www.excelforum.com/showthread...hreadid=520529 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Attaching formula to cells
Your reply solves my original problem fine thankyou and will put a 4,5,6 or 7 in column M1. What I have done is populated all of the M column from row 1 down to 100 with this formula. So now we have column M full of numbers from 4 to 7. The folowing table is going to be in column P:- =VLOOKUP(F1,'Mobile TRG Rate Card'!$A$2:$G$18,6,FALSE)*L1 What I need is the number that appears in column M to be incorperated in the above table so it looks like:- =VLOOKUP(F1,'Mobile TRG Rate Card'!$A$2:$G$18,column M,FALSE)*L1 I have used:- =VLOOKUP(F1,'Mobile TRG Rate Card'!$A$2:$G$18,$M$1,FALSE)*L1 This works fine but when I try to populate rows 1 to 100 with it, the F1 & L1 increments to F2 & L2 OK but the $M$1 stays the same? I want it to increment on to $M$2. Thats my problem. PS your working wonders so far. -- grahammal ------------------------------------------------------------------------ grahammal's Profile: http://www.excelforum.com/member.php...o&userid=20336 View this thread: http://www.excelforum.com/showthread...hreadid=520529 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Attaching formula to cells
$M$1 doesn't increment because the $ signifies an absolute reference. Remove
the $ for reference you want to change as you copy the formula to other cells. =VLOOKUP(F1,'Mobile TRG Rate Card'!$A$2:$G$18,M1,FALSE)*L1 PS the $A$2 and $G$18 references will not change either. Be sure to check that that is what you want. Since it is the table you are looking up, you will probably want them to stay the same. "grahammal" wrote: Your reply solves my original problem fine thankyou and will put a 4,5,6 or 7 in column M1. What I have done is populated all of the M column from row 1 down to 100 with this formula. So now we have column M full of numbers from 4 to 7. The folowing table is going to be in column P:- =VLOOKUP(F1,'Mobile TRG Rate Card'!$A$2:$G$18,6,FALSE)*L1 What I need is the number that appears in column M to be incorperated in the above table so it looks like:- =VLOOKUP(F1,'Mobile TRG Rate Card'!$A$2:$G$18,column M,FALSE)*L1 I have used:- =VLOOKUP(F1,'Mobile TRG Rate Card'!$A$2:$G$18,$M$1,FALSE)*L1 This works fine but when I try to populate rows 1 to 100 with it, the F1 & L1 increments to F2 & L2 OK but the $M$1 stays the same? I want it to increment on to $M$2. Thats my problem. PS your working wonders so far. -- grahammal ------------------------------------------------------------------------ grahammal's Profile: http://www.excelforum.com/member.php...o&userid=20336 View this thread: http://www.excelforum.com/showthread...hreadid=520529 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
checking that cells have a value before the workbook will close | Excel Worksheet Functions | |||
quick way to copy-paste a formula linked to cells in another file | Excel Worksheet Functions | |||
Formula works in some cells, doesn't in other | New Users to Excel | |||
trying to create an (almost) circular formula between cells and data validated cells with lists | Excel Worksheet Functions | |||
Applying formula to only NON-EMPTY cells in range | Excel Discussion (Misc queries) |