 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / May 2008Indirect
I want to create a workbook which reads the value from a particular named range (Test) in a number of workbooks. The actual formula is: ='Y:\Testing\TestSource001.xls'!Test I would like to build this formula and then read the value using the Indirect function but cannot seem to get Indirect to work on anything other than on a worksheet level. Does Indirect work for external links? I can find nothing which says it has this limitation, but I cannot find any examples of external links. Paul Smith "Paul W Smith" <p...@NOSPAM.twelve.me.uk> wrote... ... >The actual formula is: > >='Y:\Testing\TestSource001.xls'!Test ... >Does Indirect work for external links? . . . ... The simple answer is NO. The nuanced answer is yes, but only for external references into OPEN workbooks. Unless you'd have ALL these other workbooks open in the same Excel session (process), INDIRECT won't work for this. Try the following. 1. Put a list of the relevant file pathnames into a single column range, e.g., A3:A20. 2. Enter the following formula corresponding to the topmost file's pathname, e.g., in B3 corresponding to A3. ="='"&A3&"'!Test" 3. Fill this formula down so there's a like formula for every file, e.g., fill B3 down into B4:B20. These formulas should produce text strings that look like external reference formulas. 4. Select the range of formulas (B3:B20), copy, and paste special as values on top of itself. Now you should have text CONSTANTS that look like external reference formulas. 5. With this range still selected, run Edit > Replace, replacing = with = (yes, replacing the = character with itself). This effectively enters all these text constants as formulas. Thanks Harlan, Excellent solution... I have no idea how it works but it does - thanks. > "Paul W Smith" <p...@NOSPAM.twelve.me.uk> wrote... > ... [quoted text clipped - 32 lines] > with = (yes, replacing the = character with itself). This effectively > enters all these text constants as formulas. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.