Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with a bsic formula
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with a bsic formula
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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with a bsic formula
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? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with a bsic formula
You're welcome, Steve.
Pete On Jul 6, 10:05*am, stevejeffries wrote: Thanks Pete, I'll give it a try. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with a bsic formula
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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with a basic formula
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. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with a basic formula
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 On Jul 6, 12:28*pm, stevejeffries wrote: 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.- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|