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

=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

Thank you, Tom. This solution works great as long as the source file is
open. However, once I close the file I get an error. So right now I am
saving my summary spreadsheet and the open source files as a workspace. Then
I load the workspace. This leaves the source files open once I close my
summary spreadsheet. This is somewhat annoying.

Is there anyway to do this with the source files closed?

"Tom Ogilvy" wrote:

=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