using index, match on another worksheet

[elrussell]

After spending some time searching for instances of INDEX, MATCH, and
WORKSHEETS and not finding an example of what I need to do, I post this
question.

The excel code shown below works when placed in the same sheet as the
table. I need to place the code in another sheet. Here is the
information.

The table "assignTable" resides in the sheet named "StudentCatalog".
The data in the cells "P43" and "Q43" are on a sheet named "Main" (I
tested the code with the data in corresponding P43 and Q43 cells)

What I need to do is insert - correctly - [StudentCatalog!] throughout
the formula in the "Main" sheet and have it read the table in the
"StudentCatalog" sheet.



=INDEX(assignTable,MATCH(P43,INDEX(assignTable,,1),0),MATCH(Q43,INDEX(assignTable,1,),0))



I have checked many texts, tutorials, and demos. Almost all limit their
discussions to working with one sheet. Is there a general set of rules
that govern writing code within a sheet to writing within a workbook to
writing among workbooks?

Thanks in advance!

 

[Biff]

Hi!

What I need to do is insert - correctly - [StudentCatalog!] throughout
the formula in the "Main" sheet and have it read the table in the
"StudentCatalog" sheet.

Can you rephrase you post? It's not at all clear what you want.

If "assignTable" is named range in the StudentCatalog sheet, then
assignTable automatically points to the StudentCatalog sheet no matter what
sheet the formula is in.

Biff

After spending some time searching for instances of INDEX, MATCH, and
WORKSHEETS and not finding an example of what I need to do, I post this
question.

The excel code shown below works when placed in the same sheet as the
table. I need to place the code in another sheet. Here is the
information.

The table "assignTable" resides in the sheet named "StudentCatalog".
The data in the cells "P43" and "Q43" are on a sheet named "Main" (I
tested the code with the data in corresponding P43 and Q43 cells)

What I need to do is insert - correctly - [StudentCatalog!] throughout
the formula in the "Main" sheet and have it read the table in the
"StudentCatalog" sheet.



=INDEX(assignTable,MATCH(P43,INDEX(assignTable,,1),0),MATCH(Q43,INDEX(assignTable,1,),0))



I have checked many texts, tutorials, and demos. Almost all limit their
discussions to working with one sheet. Is there a general set of rules
that govern writing code within a sheet to writing within a workbook to
writing among workbooks?

Thanks in advance!