Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
how does one follow a cell link to another workbook file
Hello Excellers,
I have a worksheet cell that contains a formula that refers to a cell in another workbook file. I have a VBA macro that needs to follow that reference. That is, open the referenced file, and go to the sheet of the referenced cell (so as to get other things from the same sheet). In the past I've simply extracted the file name from the cell formula using string functions. But that only works with straightforward cell links which simply refers to a cell in the other file. That is, the cell formula only contains one file name in it somewhere. Now I'd like to handle a situation where the cell formula is more complicated. In this case the formula uses the Choose worksheet function to pick a link to another file. This means the formula contains more than a just one file name. So my method of using string functions to extract the file name doesn't cut it anymore. Is there any way to determine the file name of the cell being referenced by the more complicated cell formula? Thanks, Brian Murphy Austin, TX |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
how does one follow a cell link to another workbook file
Brian, your string functions may still work. Because the Choose function
uses an index number to select an argument, then you will need to take that value into consideration to determine which argument (reference) to extract from the formula. For example if the formula is =CHOOSE(B1,[Book1.xls]Sheet1!$A$1,[Book2.xls]Sheet1!$A$1) and the value of B1=2 then extract the reference following the 2nd comma in the formula. There is a direct relationship between the index number to Choose and the arguments which are separated by commas. Mike F wrote in message oups.com... Hello Excellers, I have a worksheet cell that contains a formula that refers to a cell in another workbook file. I have a VBA macro that needs to follow that reference. That is, open the referenced file, and go to the sheet of the referenced cell (so as to get other things from the same sheet). In the past I've simply extracted the file name from the cell formula using string functions. But that only works with straightforward cell links which simply refers to a cell in the other file. That is, the cell formula only contains one file name in it somewhere. Now I'd like to handle a situation where the cell formula is more complicated. In this case the formula uses the Choose worksheet function to pick a link to another file. This means the formula contains more than a just one file name. So my method of using string functions to extract the file name doesn't cut it anymore. Is there any way to determine the file name of the cell being referenced by the more complicated cell formula? Thanks, Brian Murphy Austin, TX |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
how does one follow a cell link to another workbook file
Thank you for the suggestion, Mike. I hadn't thought of that. That
will certainly work when the cell formula happens to use Choose, and the arguments contain the file names in explicit fashion. But it might not be using Choose, or the file names might come from yet other cells. I'm going to try to rephrase my question better. If the result of a formula in a cell produces a reference to a cell in another workbook, how can one determine the name of that file, and the worksheet in that file? Excel itself does this because it successfully fetches the value in that cell. If I use Value or Evaluate, I just get the displayed contents of the referenced cell. I think I'm looking for some clever use of Dependents or Precedents, but I have been able to find anything that works. Or maybe a way to use Evaluate on the cell formula, but to get a reference to the cell that the formula result points to. Thanks, Brian |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
HOW DO I LINK PAGES ON EXCEL TO FOLLOW SAME LINKED FORMULA? | Excel Worksheet Functions | |||
follow link | New Users to Excel | |||
How do I link data from another workbook using a different file na | Excel Discussion (Misc queries) | |||
How can I follow a Hyper-link by just pressing "Enter"? | Excel Discussion (Misc queries) | |||
Follow hyper-link by pressing "Enter" | Excel Discussion (Misc queries) |