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?

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

On Jul 5, 6:04*pm, stevejeffries
wrote:
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?

Thanks Pete, I'll give it a try.

"Pete_UK" wrote:

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

On Jul 5, 6:04 pm, stevejeffries
wrote:
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?

You're welcome, Steve.

Pete

On Jul 6, 10:05*am, stevejeffries
wrote:
Thanks Pete, I'll give it a try.

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

"Pete_UK" wrote:

You're welcome, Steve.

Pete

On Jul 6, 10:05 am, stevejeffries
wrote:
Thanks Pete, I'll give it a try.

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?

"stevejeffries" wrote:

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

"Pete_UK" wrote:

You're welcome, Steve.

Pete

On Jul 6, 10:05 am, stevejeffries
wrote:
Thanks Pete, I'll give it a try.