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

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

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

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

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

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