Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet name changes on external reference
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet name changes on external reference
=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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet name changes on external reference
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet name changes on external reference
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 "Tom Ogilvy" wrote: 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 "mikebres" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet name changes on external reference
http://tinyurl.com/9cgdc
-- Regards, Tom Ogilvy "mikebres" wrote in message ... 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 "Tom Ogilvy" wrote: 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 "mikebres" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet name changes on external reference
Thank you, Tom. It'll take me a bit to digest this.
"Tom Ogilvy" wrote: http://tinyurl.com/9cgdc -- Regards, Tom Ogilvy "mikebres" wrote in message ... 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 "Tom Ogilvy" wrote: 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 "mikebres" wrote in message ... 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet name changes on external reference
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. "mikebres" wrote: Thank you, Tom. It'll take me a bit to digest this. "Tom Ogilvy" wrote: http://tinyurl.com/9cgdc -- Regards, Tom Ogilvy "mikebres" wrote in message ... 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 "Tom Ogilvy" wrote: 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 "mikebres" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Reference external worksheet whose name is in a cell | Excel Worksheet Functions | |||
Using cell value to reference external worksheet? | Excel Worksheet Functions | |||
How do I build a reference to an external worksheet? | Excel Worksheet Functions | |||
Finding an external reference on a worksheet | Excel Discussion (Misc queries) | |||
External reference an entire worksheet | Excel Programming |