software

If Sheets(“Sheet1”).Range(“L1”) gt; 0 Or Sheets(“Sheet1”).Range(“L1”) lt; 5
Then Sheets(“Sheet1”).Range(“M1”) = 4 :End

If the above is gt; 4 or lt; 20 Then M1 = 5
If the above is gt; 19 or lt; 120 Then M1 = 6
If the above is gt; 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: www.excelforum.com/member.php...oamp;userid=20336
View this thread: www.excelforum.com/showthread...hreadid=520529In M1, enter formula:
=VLOOKUP(L1,{0,4;5,5;20,6;120,7},2,1)

That will automatically calculate M1

HTH
--
AP

quot;grahammalquot; gt; a écrit
dans le message de
...
gt;
gt; If Sheets(quot;Sheet1quot;).Range(quot;L1quot;) gt; 0 Or Sheets(quot;Sheet1quot;).Range(quot;L1quot;) lt; 5
gt; Then Sheets(quot;Sheet1quot;).Range(quot;M1quot;) = 4 :End
gt;
gt; If the above is gt; 4 or lt; 20 Then M1 = 5
gt; If the above is gt; 19 or lt; 120 Then M1 = 6
gt; If the above is gt; 119 Them M1 = 7
gt;
gt; The above are the conditions that I want to set so that the value that
gt; appears in column M is dependant on the value that is entered in column
gt; L.
gt; How do I write the formula?
gt; How do I attach it to all the rows in column M?
gt;
gt; I need this because the value that appears in column M will be used as
gt; the column number that I will use in a VLOOKUP table.
gt;
gt;
gt; --
gt; grahammal
gt; ------------------------------------------------------------------------
gt; grahammal's Profile:
www.excelforum.com/member.php...oamp;userid=20336
gt; View this thread: www.excelforum.com/showthread...hreadid=520529
gt;

=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: www.excelforum.com/member.php...oamp;userid=20336
View this thread: www.excelforum.com/showthread...hreadid=520529Your first message said:

'---------------------------------
Then Sheets(quot;Sheet1quot;).Range(quot;M1quot;) = 4 :End

If the above is gt; 4 or lt; 20 Then M1 = 5
If the above is gt; 19 or lt; 120 Then M1 = 6
If the above is gt; 119 Them M1 = 7
'------------------------------------

Now you don't want 4, 5 6 or 7 in M1 ?!?!?!

--
AP

quot;grahammalquot; gt; a écrit
dans le message de
...
gt;
gt; =VLOOKUP(L10,{0,4;5,5;20,6;120,7},2,1)
gt; The above generates a 4,5,6 or 7 and is exactly what I wanted.
gt;
gt; Next problem:-
gt; Where the above generates the required number, I need to incorperate
gt; that in the VLOOKUP table below.
gt; =VLOOKUP(F10,'Mobile TRG Rate Card'!$A$2:$G$18,6,FALSE)*L10
gt; It needs to take the place of the 6 entry which is the data column to
gt; look at.
gt; How can I insert the generated number into the table above.
gt;
gt;
gt; --
gt; grahammal
gt; ------------------------------------------------------------------------
gt; grahammal's Profile:
www.excelforum.com/member.php...oamp;userid=20336
gt; View this thread: www.excelforum.com/showthread...hreadid=520529
gt;

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 amp; L1 increments to F2 amp; 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: www.excelforum.com/member.php...oamp;userid=20336
View this thread: www.excelforum.com/showthread...hreadid=520529$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.

quot;grahammalquot; wrote:

gt;
gt; Your reply solves my original problem fine thankyou and will put a 4,5,6
gt; or 7 in column M1.
gt; What I have done is populated all of the M column from row 1 down to
gt; 100 with this formula.
gt; So now we have column M full of numbers from 4 to 7.
gt; The folowing table is going to be in column P:-
gt; =VLOOKUP(F1,'Mobile TRG Rate Card'!$A$2:$G$18,6,FALSE)*L1
gt;
gt; What I need is the number that appears in column M to be incorperated
gt; in the above table so it looks like:-
gt; =VLOOKUP(F1,'Mobile TRG Rate Card'!$A$2:$G$18,column M,FALSE)*L1
gt; I have used:-
gt; =VLOOKUP(F1,'Mobile TRG Rate Card'!$A$2:$G$18,$M$1,FALSE)*L1
gt; This works fine but when I try to populate rows 1 to 100 with it, the
gt; F1 amp; L1 increments to F2 amp; L2 OK but the $M$1 stays the same?
gt; I want it to increment on to $M$2.
gt; Thats my problem.
gt; PS your working wonders so far.
gt;
gt;
gt; --
gt; grahammal
gt; ------------------------------------------------------------------------
gt; grahammal's Profile: www.excelforum.com/member.php...oamp;userid=20336
gt; View this thread: www.excelforum.com/showthread...hreadid=520529
gt;
gt;