=INDEX(Indirect("[exp.xls]"&A1&"!C7:AJ103"),MATCH($G4,Indirect("[exp.xls]"&A1&"!C7:C103"),0),34)

Where A1 holds the sheet name.

--
Regards,
Tom Ogilvy


"mikebres" wrote:

I have a spreadsheet that summarizes information from a couple of external
workbooks. The problem is that sometimes the worksheet I am referencing has
the name NPA and sometimes it has the name VOC. (These workbooks come from
the HQ level so I can't control how the sheets are named)
I have put together a UDF that tests the workbook and returns the name of
this worksheet. However, I can't figure out how to use it in my formula on
the summary worksheet. Can someone help?
Here is the formula I am using on the summary worksheet:

=INDEX([exp.xls]NPA!$C$7:$AJ$103,MATCH($G4,[exp.xls]NPA!$C$7:$C$103,0),34)

Thanks
Mike