Worksheet name changes on external reference

[Guest]

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

 

[Guest]

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

Where A1 holds the sheet name.

 

[Guest]

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?

=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

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

 

[Tom Ogilvy]

Not an easy way. You are correct it doesn't work with a closed workbook,
but your sample formula was to an open workbook.


--
Regards,
Tom Ogilvy

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?

=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

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

 

[Guest]

True, my example was to an open workbook. I did it that way so the formula
wouldn't be so long. Also when I work with the formula I usually have the
workbook open for the same reason. I actually use it with the workbooks
closed.

So is there a way to do this with the workbooks closed?

Thanks
Mike

Not an easy way. You are correct it doesn't work with a closed workbook,
but your sample formula was to an open workbook.


--
Regards,
Tom Ogilvy

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?

=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


:

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

 

[Tom Ogilvy]

http://tinyurl.com/9cgdc

--
Regards,
Tom Ogilvy

True, my example was to an open workbook. I did it that way so the
formula
wouldn't be so long. Also when I work with the formula I usually have the
workbook open for the same reason. I actually use it with the workbooks
closed.

So is there a way to do this with the workbooks closed?

Thanks
Mike

Not an easy way. You are correct it doesn't work with a closed workbook,
but your sample formula was to an open workbook.


--
Regards,
Tom Ogilvy

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?

:

=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


:

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

 

[Guest]

Thank you, Tom. It'll take me a bit to digest this.

http://tinyurl.com/9cgdc

--
Regards,
Tom Ogilvy

True, my example was to an open workbook. I did it that way so the
formula
wouldn't be so long. Also when I work with the formula I usually have the
workbook open for the same reason. I actually use it with the workbooks
closed.

So is there a way to do this with the workbooks closed?

Thanks
Mike

Not an easy way. You are correct it doesn't work with a closed workbook,
but your sample formula was to an open workbook.


--
Regards,
Tom Ogilvy



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?

:

=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


:

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

 

[Guest]

I discovered something interesting about this. I am getting my data from a
web based database. When I download the data I have been selecting "SAVE"
rather than "OPEN".
Today I found that if I opened the file first, then saved it as an Excel
Workbook (.xls), the destination spreadsheet didn't ask for the source to be
opened.
Apparently the "Save" was using a different format, even though it told me
it was saving as an Excel file.

Go figure.

Thank you, Tom. It'll take me a bit to digest this.

http://tinyurl.com/9cgdc

--
Regards,
Tom Ogilvy

True, my example was to an open workbook. I did it that way so the
formula
wouldn't be so long. Also when I work with the formula I usually have the
workbook open for the same reason. I actually use it with the workbooks
closed.

So is there a way to do this with the workbooks closed?

Thanks
Mike

:

Not an easy way. You are correct it doesn't work with a closed workbook,
but your sample formula was to an open workbook.


--
Regards,
Tom Ogilvy



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?

:

=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


:

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