On 02/17/2011 01:21 PM, Johnny Rosenberg wrote:
Den 2011-02-17 19:47:10 skrev Wade Smart <[email protected]>:
On 02/17/2011 09:43 AM, Johnny Rosenberg wrote:
Den 2011-02-17 14:35:47 skrev Wade Smart <[email protected]>:
I have a column that will have 10 possible words in it.
Each word is assigned a number.
I have another column which calculate those words based on the numbers.
So, A1 is (Beginner, Recreational, Highschool, College, Experienced,
etc). Beginner = 1, Rec = 2 etc.
So in say B1 I want it to say 2 if I write Rec.
I was trying nested IFs but thats very complex.
Any ideas?
Wade
I would have made a list on a new sheet with two columns, like this:
Beginner 1
Recreational 2
and so on.
Then, on the first sheet, I would have used VLOOKUP() in column B.
Ok so in F2 I have "Rec", or will have as each cell is filled in, and
in G2 it needs to red "2".
L2 = Beginner
M2 = 1
L3 = Rec
M3 = 2
So this is your list with words and numbers right? I would suggest you
to sort them so the L column is in alphabetic order first.
So let's assume that you have your list, it is ten words and numbers and
it is located at L2:M11.
So if I want G to have a number in it from what I write in F, I put
the lookup in G2.
You need it in every row in the G column, starting from G2 (I assume, I
guess you use row 1 for headings or something like that, right?), going
as far down as you wish.
Now, place your cursor in G2 and press Ctrl+F2. This brings up the
formula guide. You can now, in an easy way, create your formulas.
So find VLOOKUP in the field to the left and double click it.
Now you can fill in some fields that represents the parameters of the
VLOOKUP() function.
The first field is what we want to search for, in this case whatever is
in cell F2, so click on the cell F2. If it is hidden under the dialogue,
then click the button to the right of the field to make the dialogue
hide a bit. Now click the F2 cell if you diodn't before, and then click
that button again, to the right of the input field.
The second parameter determines where we will search, which is our
L2:M11 range, so place the cursor in that field and then select that
range with the mouse.
Next parameter is called Index, and it determines in which column of our
cell range we will find our number, in this case 2. 1 means the the
first column in the selected range, in this case L, but we want our
result from the M column, so that's why 2 is the proper value here.
All fields which names are bold are required. The next one, ”Sorted”, is
not bold, so we only need it in some cases. Place the cursor there and
you will see a short description about what it does and how to use it.
As you can see, we don't need it since our cell range is sorted. Place
your cursor in one of the other fields and you should now see the result
in that dialogue.
Click OK.
So now we have a working formula in G2, but there will be some problems
when we copy our formula to other cells. One of the more serious
problems is that if we copy to G3, L2:M11 will now be ”converted” to
L3:M12.
So we need to go back to G2, press F2 and select (highlight) L2:M11 with
the mouse. Now press Shift+F4 and the range address will be ”converted”
to $L$2:$M$11, which means that the address won't changed when formula
is copied to other cells. Don't do this with the first parameter though,
since we want it to adapt when copying it.
Now copy the cell downwards.
You will now notice that if nothing is entered in one or mote of the
rows in the F column, the corresponding row in the G column will display
en error message. We don't want that, so edit the G2 formula again to this:
=IF(F2="";"";VLOOKUP(F2;$L$2:$M$11;2))
This makes sure that if F2 is empty, G2 will remain empty.
Now copy the formula downwards again, and it should work as expected,
hopefully.
The Shift +F4 didnt work but Im on Ubuntu so that might be why.
Easy enough to fix though.
Great!
And I added that If Statment. Thanks.
Wade
--
Registered Linux User: #480675
Registered Linux Machine: #408606
Linux since June 2005
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
