| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Creating a link from cell values
I am trying to streamline a database like spreadsheet.
In my worksheet each cell in successive rows in a column, references a different file depending on date in another column. All the linked paths and file names are the same with the exception of the date in the name. Is there a way to change the filename using the contents of the date cells on my worksheet. Dates: A2:A100 Linked Cells: B2:B100 (I would like each link to reference the date in adjscent cell) i.e. If the date in Cell A3 shows April 30, 2006, this date will be in the path as shown below. 'C:\Reports\Trial Balances\[Trial Balance "yyyy-m-dd".xls]. Can this be done? Any help is greatly appreciated. Cappster |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Creating a link from cell values
use concatenation by joining fragments of text and formulas ="'C:\Reports\Trial Balances\[Trial Balance "&text(A3,"yyy-m-dd")&.xls]" -- SLAnt ------------------------------------------------------------------------ SLAnt's Profile: http://www.excelforum.com/member.php...o&userid=34184 View this thread: http://www.excelforum.com/showthread...hreadid=539920 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Creating a link from cell values
You can try with HYPERLINK and TEXT, something like this:
=HYPERLINK("file://C:\Reports\Trial Balances\[Trial Balance " & TEXT(A3,"yyyy-m-dd") & ".xls]") You can add a descriptive name if you like it better, as the second parameter of HYPERLINK. Hope this helps, Miguel. "Capp" wrote: I am trying to streamline a database like spreadsheet. In my worksheet each cell in successive rows in a column, references a different file depending on date in another column. All the linked paths and file names are the same with the exception of the date in the name. Is there a way to change the filename using the contents of the date cells on my worksheet. Dates: A2:A100 Linked Cells: B2:B100 (I would like each link to reference the date in adjscent cell) i.e. If the date in Cell A3 shows April 30, 2006, this date will be in the path as shown below. 'C:\Reports\Trial Balances\[Trial Balance "yyyy-m-dd".xls]. Can this be done? Any help is greatly appreciated. Cappster |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Creating a link from cell values
This is what I have put in. ='C:\Reports\Trial Balances\["Trial Balance "&text(A13,"yyyy-m-d")&.xls]100'!$A$9 I get #REF! Is there something I am overlooking? "Miguel Zapico" wrote: You can try with HYPERLINK and TEXT, something like this: =HYPERLINK("file://C:\Reports\Trial Balances\[Trial Balance " & TEXT(A3,"yyyy-m-dd") & ".xls]") You can add a descriptive name if you like it better, as the second parameter of HYPERLINK. Hope this helps, Miguel. "Capp" wrote: I am trying to streamline a database like spreadsheet. In my worksheet each cell in successive rows in a column, references a different file depending on date in another column. All the linked paths and file names are the same with the exception of the date in the name. Is there a way to change the filename using the contents of the date cells on my worksheet. Dates: A2:A100 Linked Cells: B2:B100 (I would like each link to reference the date in adjscent cell) i.e. If the date in Cell A3 shows April 30, 2006, this date will be in the path as shown below. 'C:\Reports\Trial Balances\[Trial Balance "yyyy-m-dd".xls]. Can this be done? Any help is greatly appreciated. Cappster |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Creating a link from cell values
It is because of the punctuaction. Try this version of your formula:
="'C:\Reports\Trial Balances\[""Trial Balance"""&TEXT(A13,"yyyy-m-d")&".xls]100'!$A$9" Miguel. "Capp" wrote: This is what I have put in. ='C:\Reports\Trial Balances\["Trial Balance "&text(A13,"yyyy-m-d")&.xls]100'!$A$9 I get #REF! Is there something I am overlooking? "Miguel Zapico" wrote: You can try with HYPERLINK and TEXT, something like this: =HYPERLINK("file://C:\Reports\Trial Balances\[Trial Balance " & TEXT(A3,"yyyy-m-dd") & ".xls]") You can add a descriptive name if you like it better, as the second parameter of HYPERLINK. Hope this helps, Miguel. "Capp" wrote: I am trying to streamline a database like spreadsheet. In my worksheet each cell in successive rows in a column, references a different file depending on date in another column. All the linked paths and file names are the same with the exception of the date in the name. Is there a way to change the filename using the contents of the date cells on my worksheet. Dates: A2:A100 Linked Cells: B2:B100 (I would like each link to reference the date in adjscent cell) i.e. If the date in Cell A3 shows April 30, 2006, this date will be in the path as shown below. 'C:\Reports\Trial Balances\[Trial Balance "yyyy-m-dd".xls]. Can this be done? Any help is greatly appreciated. Cappster |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Creating a link from cell values
Thanks for sticking with this problem. Your time is very much appreciated!
However, with this formula you gave me, the formula just shows up as the value. Are the quotes at the beginning and end doing this? When I take the first and last quotes out I get the same reference error as before? ='C:\Reports\Trial Balances\[""Trial Balance"""&TEXT(A14,"yyyy-m-d")&".xls]100'!$A$9 "Miguel Zapico" wrote: It is because of the punctuaction. Try this version of your formula: ="'C:\Reports\Trial Balances\[""Trial Balance"""&TEXT(A13,"yyyy-m-d")&".xls]100'!$A$9" Miguel. "Capp" wrote: This is what I have put in. ='C:\Reports\Trial Balances\["Trial Balance "&text(A13,"yyyy-m-d")&.xls]100'!$A$9 I get #REF! Is there something I am overlooking? "Miguel Zapico" wrote: You can try with HYPERLINK and TEXT, something like this: =HYPERLINK("file://C:\Reports\Trial Balances\[Trial Balance " & TEXT(A3,"yyyy-m-dd") & ".xls]") You can add a descriptive name if you like it better, as the second parameter of HYPERLINK. Hope this helps, Miguel. "Capp" wrote: I am trying to streamline a database like spreadsheet. In my worksheet each cell in successive rows in a column, references a different file depending on date in another column. All the linked paths and file names are the same with the exception of the date in the name. Is there a way to change the filename using the contents of the date cells on my worksheet. Dates: A2:A100 Linked Cells: B2:B100 (I would like each link to reference the date in adjscent cell) i.e. If the date in Cell A3 shows April 30, 2006, this date will be in the path as shown below. 'C:\Reports\Trial Balances\[Trial Balance "yyyy-m-dd".xls]. Can this be done? Any help is greatly appreciated. Cappster |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Creating a link from cell values
use the indirect function =indirect() putting that lot inside the brackets somethinglikeant -- SLAnt ------------------------------------------------------------------------ SLAnt's Profile: http://www.excelforum.com/member.php...o&userid=34184 View this thread: http://www.excelforum.com/showthread...hreadid=539920 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Creating a link from cell values
Does the file need to be open for this function to work?
"SLAnt" wrote: use the indirect function =indirect() putting that lot inside the brackets somethinglikeant -- SLAnt ------------------------------------------------------------------------ SLAnt's Profile: http://www.excelforum.com/member.php...o&userid=34184 View this thread: http://www.excelforum.com/showthread...hreadid=539920 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Creating a link from cell values
Yes.
Harlan Grove wrote a UDF called PULL that will retrieve the value from a closed workbook. You can find the function at Harlan's FTP site: ftp://members.aol.com/hrlngrv/ Look for pull.zip Laurent Longre has an addin (morefunc.xll) at: http://xcell05.free.fr/ That includes =indirect.ext() that may help you. Capp wrote: Does the file need to be open for this function to work? "SLAnt" wrote: use the indirect function =indirect() putting that lot inside the brackets somethinglikeant -- SLAnt ------------------------------------------------------------------------ SLAnt's Profile: http://www.excelforum.com/member.php...o&userid=34184 View this thread: http://www.excelforum.com/showthread...hreadid=539920 -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Refreshing drop down cell values... | Excel Worksheet Functions | |||
| Use Cell Content to Build Link | Excel Worksheet Functions | |||
| copy combobox - cell link to change automatically | Excel Worksheet Functions | |||
| Copy cell format to cell on another worksht and update automatical | Excel Worksheet Functions | |||
| up to 7 functions? | Excel Worksheet Functions | |||
Hybrid Mode
