Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Linking a Specific Word to a Specific Number
Hell all,
I have a workbook which contains several worksheets. Each worksheet contains specific data as text with an ID number. i.e. Hip ID number for this wrod is 2. The word Hip and its ID number are located in the Body Part worksheet. There's a main worksheet named Map, which is where I would like to be able to tyoe the word Hip and have the ID number automatically appear in a specific cell in the Map worksheet. This is the Body Part worksheet Body Part ID Body Part 1 Shoulder 2 Hip 3 Knee This is the map: Category ID Body Part Complaint Element Group Question 2 (cell b6) Hip (cell I6) I would like to type the word Hip under body part on cell I6 and have the number 2 appear automatically in the Category ID cell b6. Thanks in advance |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Linking a Specific Word to a Specific Number
I'm going to assume that on your body part worksheet that the Body Part ID is
in column A, and the Body Part (name) is in column B. I am also going to assume that the sheet's name is Parts List in B6 type this formula: =OFFSET('Parts List'!$A$1,MATCH(I6,'Parts List'!$B$1:$B$3,0)-1,0) The use of the $ signs is important - it keeps the references to the column ranges on the Parts List sheet from changing as you copy the formula to more cells on the other sheet. But you don't want $ signs in the "I6" reference so that it will automatically adjust itself as you extend the formula up/down the sheet there. Change Parts List to be the real sheet's name, and change $B$3 to be the last cell with a body part name in it on that sheet. To keep #NA from popping up in cells before you've entered a body part name, alter that formula to look like this: =IF(ISNA(OFFSET('Parts List'!$A$1,MATCH(I6,'Parts List'!$B$1:$B$3,0)-1,0)),"",OFFSET('Parts List'!$A$1,MATCH(I6,'Parts List'!$B$1:$B$3,0)-1,0)) I recommend that you use a list with data validation in the 'I' column to select the body part from to prevent misspellings from returning erroneous results. " wrote: Hell all, I have a workbook which contains several worksheets. Each worksheet contains specific data as text with an ID number. i.e. Hip ID number for this wrod is 2. The word Hip and its ID number are located in the Body Part worksheet. There's a main worksheet named Map, which is where I would like to be able to tyoe the word Hip and have the ID number automatically appear in a specific cell in the Map worksheet. This is the Body Part worksheet Body Part ID Body Part 1 Shoulder 2 Hip 3 Knee This is the map: Category ID Body Part Complaint Element Group Question 2 (cell b6) Hip (cell I6) I would like to type the word Hip under body part on cell I6 and have the number 2 appear automatically in the Category ID cell b6. Thanks in advance |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pasting charts to Word from Excel as picture | Charts and Charting in Excel | |||
count the number of times a specific word appears in a column | Excel Worksheet Functions | |||
Pulling Data off Web - Need Function Help | Excel Worksheet Functions | |||
how do I find an average number of specific words in a column | New Users to Excel | |||
getting specific info from a word document into excel | Excel Discussion (Misc queries) |