Help with a bsic formula

[stevejeffries]

I am trying to write a formula, but, have got stuck as I haven't used Excel
for a very long time. Hope someone can help me with this one - it is for my
son's school and is based on the game of Cluedo:-
Sheet 1 - column 1 will be a list of names (suspects)
column 2 will be an item (clock, chair etc)
column 3 will be a location (playground, office etc).
So, A1 might read 'Mr Smith', B1 might read 'clock', C1 might read
'playground' - meaning that Mr Smith was hit with a clock in the playground.

On sheet 2 I have one column listing 14 items and one column listing 14
locations.
The formula I want is possibly an 'IF', but, I'm not sure. I want to be able
to type letters A - N in cells in column B and get a return from the list of
'items' on sheet 2, i.e. If I type 'A' in cell B1 I want it to return
'Computer' in the cell.

Can anyone help please?

 

[Pete_UK]

Assume Items are in cells I1:I14 on sheet2, you could do it like this:

=INDEX(Sheet2!I$1:I$14,CODE(B1)-64)

Hope this helps.

Pete

 

[stevejeffries]

Thanks Pete, I'll give it a try.

Assume Items are in cells I1:I14 on sheet2, you could do it like this:

=INDEX(Sheet2!I$1:I$14,CODE(B1)-64)

Hope this helps.

Pete

 

[Pete_UK]

You're welcome, Steve.

Pete

 

[stevejeffries]

Pete, as suggested I placed the 'items' in cells I1 to I14 on page 2, and
then used column C for data input (A,B,C etc). The formula entered in cell B2
is '=INDEX(Sheet2!I$1:I$14,CODE(C2)-64) and this is working OK (if I enter F
in C2 then CLOCK is shown in B2 - this is exactly what I'm after.

My next problem is that I am now trying the same thing with column D. The
formula used is '=INDEX(Sheet2!K$1:K$14,CODE(E2)-64)' - column E is used to
input data and I have a list of 'Locations' on page 2 in cells K1:K14 - this
formula isn't working. Any ideas please?

Steve

 

[stevejeffries]

Pete - just to add that when inputing in column E I'll be inputing numbers
rather that letters (1,2,3 instead of A,B,C etc). Will a formula work if I
input numbers instead of letters?

 

[Pete_UK]

Hi Steve,

the part of the formula I gave you that has CODE(C2)-64 is converting
the character in C2 into a number - if C2 contains the letter A, for
example, this has a code of 65 so this part converts it to the number
1.

So, if you are entering numbers into column E, then you should be able
to simplify the formula to:

=INDEX(Sheet2!K$1:K$14,E2)

You might want to go a bit further - if the formula is in place but
you haven't entered anything into E2, you will get an error. You could
avoid this with a slight amendment:

=IF(E2="","",INDEX(Sheet2!K$1:K$14,E2))

i.e. if the cell is blank then return a blank. You should also check
for E2 not being above 14 (or apply data validation to that cell to
ensure this does not happen).

Hope this helps.

Pete