software

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 quot;yyyy-m-ddquot;.xls].

Can this be done?

Any help is greatly appreciated.

Cappster


use concatenation by joining fragments of text and formulas
=quot;'C:\Reports\Trial Balances\[Trial Balance
quot;amp;text(A3,quot;yyy-m-ddquot;)amp;.xls]quot;--
SLAnt
------------------------------------------------------------------------
SLAnt's Profile: www.excelforum.com/member.php...oamp;userid=34184
View this thread: www.excelforum.com/showthread...hreadid=539920You can try with HYPERLINK and TEXT, something like this:
=HYPERLINK(quot;file://C:\Reports\Trial Balances\[Trial Balance quot; amp;
TEXT(A3,quot;yyyy-m-ddquot;) amp; quot;.xls]quot;)
You can add a descriptive name if you like it better, as the second
parameter of HYPERLINK.

Hope this helps,
Miguel.

quot;Cappquot; wrote:

gt; I am trying to streamline a database like spreadsheet.
gt; In my worksheet each cell in successive rows in a column, references a
gt; different file depending on date in another column. All the linked paths and
gt; file names are the same with the exception of the date in the name. Is there
gt; a way to change the filename using the contents of the date cells on my
gt; worksheet.
gt;
gt; Dates: A2:A100
gt; Linked Cells: B2:B100 (I would like each link to reference the date in
gt; adjscent cell)
gt;
gt; i.e. If the date in Cell A3 shows April 30, 2006, this date will be in the
gt; path as shown below.
gt;
gt; 'C:\Reports\Trial Balances\[Trial Balance quot;yyyy-m-ddquot;.xls].
gt;
gt; Can this be done?
gt;
gt; Any help is greatly appreciated.
gt;
gt; Cappster


This is what I have put in.

='C:\Reports\Trial Balances\[quot;Trial Balance
quot;amp;text(A13,quot;yyyy-m-dquot;)amp;.xls]100'!$A$9

I get #REF!

Is there something I am overlooking?

quot;Miguel Zapicoquot; wrote:

gt; You can try with HYPERLINK and TEXT, something like this:
gt; =HYPERLINK(quot;file://C:\Reports\Trial Balances\[Trial Balance quot; amp;
gt; TEXT(A3,quot;yyyy-m-ddquot;) amp; quot;.xls]quot;)
gt; You can add a descriptive name if you like it better, as the second
gt; parameter of HYPERLINK.
gt;
gt; Hope this helps,
gt; Miguel.
gt;
gt; quot;Cappquot; wrote:
gt;
gt; gt; I am trying to streamline a database like spreadsheet.
gt; gt; In my worksheet each cell in successive rows in a column, references a
gt; gt; different file depending on date in another column. All the linked paths and
gt; gt; file names are the same with the exception of the date in the name. Is there
gt; gt; a way to change the filename using the contents of the date cells on my
gt; gt; worksheet.
gt; gt;
gt; gt; Dates: A2:A100
gt; gt; Linked Cells: B2:B100 (I would like each link to reference the date in
gt; gt; adjscent cell)
gt; gt;
gt; gt; i.e. If the date in Cell A3 shows April 30, 2006, this date will be in the
gt; gt; path as shown below.
gt; gt;
gt; gt; 'C:\Reports\Trial Balances\[Trial Balance quot;yyyy-m-ddquot;.xls].
gt; gt;
gt; gt; Can this be done?
gt; gt;
gt; gt; Any help is greatly appreciated.
gt; gt;
gt; gt; Cappster

It is because of the punctuaction. Try this version of your formula:
=quot;'C:\Reports\Trial Balances\[quot;quot;Trial
Balancequot;quot;quot;amp;TEXT(A13,quot;yyyy-m-dquot;)amp;quot;.xls]100'!$A$9quot;

Miguel.

quot;Cappquot; wrote:

gt;
gt; This is what I have put in.
gt;
gt; ='C:\Reports\Trial Balances\[quot;Trial Balance
gt; quot;amp;text(A13,quot;yyyy-m-dquot;)amp;.xls]100'!$A$9
gt;
gt; I get #REF!
gt;
gt; Is there something I am overlooking?
gt;
gt; quot;Miguel Zapicoquot; wrote:
gt;
gt; gt; You can try with HYPERLINK and TEXT, something like this:
gt; gt; =HYPERLINK(quot;file://C:\Reports\Trial Balances\[Trial Balance quot; amp;
gt; gt; TEXT(A3,quot;yyyy-m-ddquot;) amp; quot;.xls]quot;)
gt; gt; You can add a descriptive name if you like it better, as the second
gt; gt; parameter of HYPERLINK.
gt; gt;
gt; gt; Hope this helps,
gt; gt; Miguel.
gt; gt;
gt; gt; quot;Cappquot; wrote:
gt; gt;
gt; gt; gt; I am trying to streamline a database like spreadsheet.
gt; gt; gt; In my worksheet each cell in successive rows in a column, references a
gt; gt; gt; different file depending on date in another column. All the linked paths and
gt; gt; gt; file names are the same with the exception of the date in the name. Is there
gt; gt; gt; a way to change the filename using the contents of the date cells on my
gt; gt; gt; worksheet.
gt; gt; gt;
gt; gt; gt; Dates: A2:A100
gt; gt; gt; Linked Cells: B2:B100 (I would like each link to reference the date in
gt; gt; gt; adjscent cell)
gt; gt; gt;
gt; gt; gt; i.e. If the date in Cell A3 shows April 30, 2006, this date will be in the
gt; gt; gt; path as shown below.
gt; gt; gt;
gt; gt; gt; 'C:\Reports\Trial Balances\[Trial Balance quot;yyyy-m-ddquot;.xls].
gt; gt; gt;
gt; gt; gt; Can this be done?
gt; gt; gt;
gt; gt; gt; Any help is greatly appreciated.
gt; gt; gt;
gt; gt; gt; Cappster


use the indirect function
=indirect() putting that lot inside the brackets

somethinglikeant--
SLAnt
------------------------------------------------------------------------
SLAnt's Profile: www.excelforum.com/member.php...oamp;userid=34184
View this thread: www.excelforum.com/showthread...hreadid=539920Thanks 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\[quot;quot;Trial
Balancequot;quot;quot;amp;TEXT(A14,quot;yyyy-m-dquot;)amp;quot;.xls]100'!$A$9

quot;Miguel Zapicoquot; wrote:

gt; It is because of the punctuaction. Try this version of your formula:
gt; =quot;'C:\Reports\Trial Balances\[quot;quot;Trial
gt; Balancequot;quot;quot;amp;TEXT(A13,quot;yyyy-m-dquot;)amp;quot;.xls]100'!$A$9quot;
gt;
gt; Miguel.
gt;
gt; quot;Cappquot; wrote:
gt;
gt; gt;
gt; gt; This is what I have put in.
gt; gt;
gt; gt; ='C:\Reports\Trial Balances\[quot;Trial Balance
gt; gt; quot;amp;text(A13,quot;yyyy-m-dquot;)amp;.xls]100'!$A$9
gt; gt;
gt; gt; I get #REF!
gt; gt;
gt; gt; Is there something I am overlooking?
gt; gt;
gt; gt; quot;Miguel Zapicoquot; wrote:
gt; gt;
gt; gt; gt; You can try with HYPERLINK and TEXT, something like this:
gt; gt; gt; =HYPERLINK(quot;file://C:\Reports\Trial Balances\[Trial Balance quot; amp;
gt; gt; gt; TEXT(A3,quot;yyyy-m-ddquot;) amp; quot;.xls]quot;)
gt; gt; gt; You can add a descriptive name if you like it better, as the second
gt; gt; gt; parameter of HYPERLINK.
gt; gt; gt;
gt; gt; gt; Hope this helps,
gt; gt; gt; Miguel.
gt; gt; gt;
gt; gt; gt; quot;Cappquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; I am trying to streamline a database like spreadsheet.
gt; gt; gt; gt; In my worksheet each cell in successive rows in a column, references a
gt; gt; gt; gt; different file depending on date in another column. All the linked paths and
gt; gt; gt; gt; file names are the same with the exception of the date in the name. Is there
gt; gt; gt; gt; a way to change the filename using the contents of the date cells on my
gt; gt; gt; gt; worksheet.
gt; gt; gt; gt;
gt; gt; gt; gt; Dates: A2:A100
gt; gt; gt; gt; Linked Cells: B2:B100 (I would like each link to reference the date in
gt; gt; gt; gt; adjscent cell)
gt; gt; gt; gt;
gt; gt; gt; gt; i.e. If the date in Cell A3 shows April 30, 2006, this date will be in the
gt; gt; gt; gt; path as shown below.
gt; gt; gt; gt;
gt; gt; gt; gt; 'C:\Reports\Trial Balances\[Trial Balance quot;yyyy-m-ddquot;.xls].
gt; gt; gt; gt;
gt; gt; gt; gt; Can this be done?
gt; gt; gt; gt;
gt; gt; gt; gt; Any help is greatly appreciated.
gt; gt; gt; gt;
gt; gt; gt; gt; Cappster

Does the file need to be open for this function to work?

quot;SLAntquot; wrote:

gt;
gt; use the indirect function
gt; =indirect() putting that lot inside the brackets
gt;
gt; somethinglikeant
gt;
gt;
gt; --
gt; SLAnt
gt; ------------------------------------------------------------------------
gt; SLAnt's Profile: www.excelforum.com/member.php...oamp;userid=34184
gt; View this thread: www.excelforum.com/showthread...hreadid=539920
gt;
gt;

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:
xcell05.free.fr/

That includes =indirect.ext() that may help you.

Capp wrote:
gt;
gt; Does the file need to be open for this function to work?
gt;
gt; quot;SLAntquot; wrote:
gt;
gt; gt;
gt; gt; use the indirect function
gt; gt; =indirect() putting that lot inside the brackets
gt; gt;
gt; gt; somethinglikeant
gt; gt;
gt; gt;
gt; gt; --
gt; gt; SLAnt
gt; gt; ------------------------------------------------------------------------
gt; gt; SLAnt's Profile: www.excelforum.com/member.php...oamp;userid=34184
gt; gt; View this thread: www.excelforum.com/showthread...hreadid=539920
gt; gt;
gt; gt;

--

Dave Peterson