 |
 | 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 / New Users / July 2006Dynamically changing a link name
Dear All, is it possible to include a variable in a formula that gives you the option to pick out a different file name? eg.. Weekly Reports are created with just the last 6 characters of the file name changing each time a new report is created. ... eg. WR010106, WR070106 etc.. Can I have a cell ("A1") contain the variable '070106' that gets picked up from a formula in "B1" where the formula may be: = Vlookup($A2,'[WRXXXXXX.xls]Sheet1'!$A$1:$D$50,4,false) where the XXXXXX represents the value in "A1"? Basically, enter a string in one cell that gets picked up by a formula containing a link. I suppose I am trying to bypass the 'Change Source' option in the Links area, and making the formula more dynamic. Kind regards... andym I have half answered my own question with: =VLOOKUP(B5,(INDIRECT("[Test" & E1 & ".xls]Sheet1!$B$4:$C$4")),2,FALSE) (different references used, but idea the same!!). Only problem with this is that the linked sheet has to be open. Is there any way around this? There will be instances where the linked sheet will not be open. Regards, andym > Dear All, > [quoted text clipped - 21 lines] > > andym = Vlookup($A2,INDIRECT("'[WR" & A1 & ".xls]Sheet1'!$A$1:$D$50"),4,false) Does something like that work? Die_Another_Day > Dear All, > [quoted text clipped - 21 lines] > > andym DAD, yes it does, but only if the WRXXXXXX file is open. I may have to make a comprimise here!!! thanks.. andym > = Vlookup($A2,INDIRECT("'[WR" & A1 & ".xls]Sheet1'!$A$1:$D$50"),4,false) > Does something like that work? [quoted text clipped - 26 lines] > > > > andym Have a look at Laurent Longre's MOREFUNC add-in, that provides an INDIRECT.EXT function, http://xcell05.free.fr/  SignatureHTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) > DAD, > [quoted text clipped - 36 lines] > > > > > > andym
|
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.