Actually this work for sheet 1 only I need it to search all 16 worksheet in
the same workbook. But otherwise this worked.

"Niek Otten" wrote:

Use

INDEX('[customer price level4.xls]Sheet1'!$A2:$C2,3)

Note the omitted $ sings

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"klafert" wrote in message ...
|I have a spreadsheet that I am using the following formula and it works:
|
| =INDEX('[customer price level4.xls]Sheet1'!$A$2:$C$2,3)
|
| I have the formula in C2 and trying to copy the formula in the column
| C3..c1000, but when I copy it or use fill down, then it doesn't change.
| Example:
|
| C2 =INDEX('[customer price level4.xls]Sheet1'!$A$2:$C$2,3)
| C3 =INDEX('[customer price level4.xls]Sheet1'!$A$3:$C$3,3)
| C4 =INDEX('[customer price level4.xls]Sheet1'!$A$4:$C$4,3)
| C5 =INDEX('[customer price level4.xls]Sheet1'!$A$5:$C$5,3) and so on....
|
| I will be doing this often and the range will change every week. which is
| no problem but I need to be able to copy the formula and have it adjust to
| the cells w/o me having to go to each row. I thought when you copy it
| normally did that? Is it my formula? I am new to using Index. Also, the
| source sheet has 16 worksheet. Tabs sheet1, sheet2, and so on until sheet16,
| and from a1..c65536. So I need to adjust my range also, but I was doing a
| test range. But I am not sure how to reference several worksheet in one
| workbook?
|
| Thanks for any help and hopefully today!! Need to have the 1st spreadsheet
| done today.