Prev: Help with blank cells when concatenating
Next: FINDING THE LAST ENTERED NUMBER IN A ROW OF DATA
From: stevejeffries on 5 Jul 2008 13:04
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?

From: Pete_UK on 5 Jul 2008 13:16
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
<stevejeffr...(a)discussions.microsoft.com> 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?

From: stevejeffries on 6 Jul 2008 05:05
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
> <stevejeffr...(a)discussions.microsoft.com> 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?
>
>
From: Pete_UK on 6 Jul 2008 06:17
You're welcome, Steve.

Pete

On Jul 6, 10:05 am, stevejeffries
<stevejeffr...(a)discussions.microsoft.com> wrote:
> Thanks Pete, I'll give it a try.
>
From: stevejeffries on 6 Jul 2008 07:21
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
> <stevejeffr...(a)discussions.microsoft.com> wrote:
> > Thanks Pete, I'll give it a try.
> >
>
 | 
Pages: 1
Prev: Help with blank cells when concatenating
Next: FINDING THE LAST ENTERED NUMBER IN A ROW OF DATA