excel formula (index, match) issue

Hope I am posting in correct place?? Sorry if not.
I'm using excel 2003 & have a sheet open with two worksheets in it.
My question is about writing the formula (below formula is typical) in one worksheet which will refer to information in the other worksheet.
Can it be done? Do I need to insert the name of the other worksheet somewhere into the formuls? Any help appreciated.

=INDEX(A2:H50,MATCH( "custom ",A2:A50,0),2)

Thanks all

nijinski

 

Yes.

If you want to refer to cells on a given sheet you use Name! before the cell reference(s) on that sheet, where Name is the name of the named sheet.

Simple example. To set A1 on Sheet1 of a workbook equal to B6 on sheet2.

In A1 of Sheet1, type =Sheet2!B6

Actually, Excel will do this automatically, if you insert the cell references by going to the second sheet and selecting the required cell(s).

In my example, on Sheet1, cell A1 Type = , then click on sheet2 tab to open it and click on cell B6, press enter. If you now look at the formula in cell A1 of Sheet1, you'll see that it shows =Sheet2!B6.

Similarly to enter a range of cells in a formula, say A1 to C10, type Name!A1:C10 (where again, Name! is the sheet which contains the range you want to refer to), or while in the formula at the place where you want to refer to a range on another sheet, click the tab of that sheet, select the required range, press enter.

Example, on Sheet1 in cell A1, type =SUM( , then click tab of sheet2 select a range, for example A1 to C10, press enter. The formiula in A1 of sheet1 will read =SUM(Sheet2!A1:C10)