assemble link from different cells and return value

Hey there, this is my first time on a forum....

I need to link from a worksheet to another file. Instead of creating this link normally (just by opening the other file and selecting the desired cell), I am trying to assemble the link from a number of different cells in different worksheets...

for example.

Cell A1 in worksheet1 = long file path

something like - file://nuefil08\nuefeb4\Work\Data Entry\31.03.05\EM22.xls

Cell A1 in worksheet2 = worksheet name and cell reference

something like - Monthy Totals Line 1!C$159

The link should be the sum of the 2 cells, however whatever I have tried, excel only returns the text string, not the actual value of the cell from the link...

I've been trying everything for 2 days and it's starting to do my head in... Any help would be appreciated! Cheers...

I think you want to use the =indirect() worksheet function.

But the bad news is that this won't work when the workbook is closed.

Harlan Grove wrote a UDF called 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

tastic96 wrote:
>
> Hey there, this is my first time on a forum....
>
> I need to link from a worksheet to another file. Instead of creating
> this link normally (just by opening the other file and selecting the
> desired cell), I am trying to assemble the link from a number of
> different cells in different worksheets...
>
> for example.
>
> Cell A1 in worksheet1 = long file path
>
> something like - file://nuefil08\nuefeb4\Work\Data
> Entry\31.03.05\EM22.xls
>
> Cell A1 in worksheet2 = worksheet name and cell reference
>
> something like - Monthy Totals Line 1!C$159
>
> The link should be the sum of the 2 cells, however whatever I have
> tried, excel only returns the text string, not the actual value of the
> cell from the link...
>
> I've been trying everything for 2 days and it's starting to do my head
> in... Any help would be appreciated! Cheers...
>
> --
> tastic96
> ------------------------------------------------------------------------
> tastic96's Profile: http://www.excelforum.com/member.php...o&userid=24662
> View this thread: http://www.excelforum.com/showthread...hreadid=382405

--

Dave Peterson

slight confusion, when you say "returns the text string", is this a text
string of the equation, or a concatination of the items in the cells.
if it is the equation, does it let you leave the cell?
if so, the cell is probably formated as text and you need to make sure it
is formated as general. if it is formated for text , check that there is no
space or hyphen in front of the "="
if not, it is saying there is an error in the equation. post a copy, and
see if somone can help you with the format.

I doubt it is the concatination, but if so, please post the equation.



"tastic96" wrote:

>
> Hey there, this is my first time on a forum....
>
> I need to link from a worksheet to another file. Instead of creating
> this link normally (just by opening the other file and selecting the
> desired cell), I am trying to assemble the link from a number of
> different cells in different worksheets...
>
> for example.
>
> Cell A1 in worksheet1 = long file path
>
> something like - file://nuefil08\nuefeb4\Work\Data
> Entry\31.03.05\EM22.xls
>
> Cell A1 in worksheet2 = worksheet name and cell reference
>
> something like - Monthy Totals Line 1!C$159
>
> The link should be the sum of the 2 cells, however whatever I have
> tried, excel only returns the text string, not the actual value of the
> cell from the link...
>
> I've been trying everything for 2 days and it's starting to do my head
> in... Any help would be appreciated! Cheers...
>
>
> --
> tastic96
> ------------------------------------------------------------------------
> tastic96's Profile: http://www.excelforum.com/member.php...o&userid=24662
> View this thread: http://www.excelforum.com/showthread...hreadid=382405
>
>

I think you want to use the =indirect() worksheet function.

But the bad news is that this won't work when the workbook is closed.

Harlan Grove wrote a UDF called 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

tastic96 wrote:
>
> Hey there, this is my first time on a forum....
>
> I need to link from a worksheet to another file. Instead of creating
> this link normally (just by opening the other file and selecting the
> desired cell), I am trying to assemble the link from a number of
> different cells in different worksheets...
>
> for example.
>
> Cell A1 in worksheet1 = long file path
>
> something like - file://nuefil08\nuefeb4\Work\Data
> Entry\31.03.05\EM22.xls
>
> Cell A1 in worksheet2 = worksheet name and cell reference
>
> something like - Monthy Totals Line 1!C$159
>
> The link should be the sum of the 2 cells, however whatever I have
> tried, excel only returns the text string, not the actual value of the
> cell from the link...
>
> I've been trying everything for 2 days and it's starting to do my head
> in... Any help would be appreciated! Cheers...
>
> --
> tastic96
> ------------------------------------------------------------------------
> tastic96's Profile: http://www.excelforum.com/member.php...o&userid=24662
> View this thread: http://www.excelforum.com/showthread...hreadid=382405

--

Dave Peterson

I think you want to use the =indirect() worksheet function.

But the bad news is that this won't work when the workbook is closed.

Harlan Grove wrote a UDF called 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

tastic96 wrote:
>
> Hey there, this is my first time on a forum....
>
> I need to link from a worksheet to another file. Instead of creating
> this link normally (just by opening the other file and selecting the
> desired cell), I am trying to assemble the link from a number of
> different cells in different worksheets...
>
> for example.
>
> Cell A1 in worksheet1 = long file path
>
> something like - file://nuefil08\nuefeb4\Work\Data
> Entry\31.03.05\EM22.xls
>
> Cell A1 in worksheet2 = worksheet name and cell reference
>
> something like - Monthy Totals Line 1!C$159
>
> The link should be the sum of the 2 cells, however whatever I have
> tried, excel only returns the text string, not the actual value of the
> cell from the link...
>
> I've been trying everything for 2 days and it's starting to do my head
> in... Any help would be appreciated! Cheers...
>
> --
> tastic96
> ------------------------------------------------------------------------
> tastic96's Profile: http://www.excelforum.com/member.php...o&userid=24662
> View this thread: http://www.excelforum.com/showthread...hreadid=382405

--

Dave Peterson

slight confusion, when you say "returns the text string", is this a text
string of the equation, or a concatination of the items in the cells.
if it is the equation, does it let you leave the cell?
if so, the cell is probably formated as text and you need to make sure it
is formated as general. if it is formated for text , check that there is no
space or hyphen in front of the "="
if not, it is saying there is an error in the equation. post a copy, and
see if somone can help you with the format.

I doubt it is the concatination, but if so, please post the equation.



"tastic96" wrote:

>
> Hey there, this is my first time on a forum....
>
> I need to link from a worksheet to another file. Instead of creating
> this link normally (just by opening the other file and selecting the
> desired cell), I am trying to assemble the link from a number of
> different cells in different worksheets...
>
> for example.
>
> Cell A1 in worksheet1 = long file path
>
> something like - file://nuefil08\nuefeb4\Work\Data
> Entry\31.03.05\EM22.xls
>
> Cell A1 in worksheet2 = worksheet name and cell reference
>
> something like - Monthy Totals Line 1!C$159
>
> The link should be the sum of the 2 cells, however whatever I have
> tried, excel only returns the text string, not the actual value of the
> cell from the link...
>
> I've been trying everything for 2 days and it's starting to do my head
> in... Any help would be appreciated! Cheers...
>
>
> --
> tastic96
> ------------------------------------------------------------------------
> tastic96's Profile: http://www.excelforum.com/member.php...o&userid=24662
> View this thread: http://www.excelforum.com/showthread...hreadid=382405
>
>

slight confusion, when you say "returns the text string", is this a text
string of the equation, or a concatination of the items in the cells.
if it is the equation, does it let you leave the cell?
if so, the cell is probably formated as text and you need to make sure it
is formated as general. if it is formated for text , check that there is no
space or hyphen in front of the "="
if not, it is saying there is an error in the equation. post a copy, and
see if somone can help you with the format.

I doubt it is the concatination, but if so, please post the equation.



"tastic96" wrote:

>
> Hey there, this is my first time on a forum....
>
> I need to link from a worksheet to another file. Instead of creating
> this link normally (just by opening the other file and selecting the
> desired cell), I am trying to assemble the link from a number of
> different cells in different worksheets...
>
> for example.
>
> Cell A1 in worksheet1 = long file path
>
> something like - file://nuefil08\nuefeb4\Work\Data
> Entry\31.03.05\EM22.xls
>
> Cell A1 in worksheet2 = worksheet name and cell reference
>
> something like - Monthy Totals Line 1!C$159
>
> The link should be the sum of the 2 cells, however whatever I have
> tried, excel only returns the text string, not the actual value of the
> cell from the link...
>
> I've been trying everything for 2 days and it's starting to do my head
> in... Any help would be appreciated! Cheers...
>
>
> --
> tastic96
> ------------------------------------------------------------------------
> tastic96's Profile: http://www.excelforum.com/member.php...o&userid=24662
> View this thread: http://www.excelforum.com/showthread...hreadid=382405
>
>

I think you want to use the =indirect() worksheet function.

But the bad news is that this won't work when the workbook is closed.

Harlan Grove wrote a UDF called 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

tastic96 wrote:
>
> Hey there, this is my first time on a forum....
>
> I need to link from a worksheet to another file. Instead of creating
> this link normally (just by opening the other file and selecting the
> desired cell), I am trying to assemble the link from a number of
> different cells in different worksheets...
>
> for example.
>
> Cell A1 in worksheet1 = long file path
>
> something like - file://nuefil08\nuefeb4\Work\Data
> Entry\31.03.05\EM22.xls
>
> Cell A1 in worksheet2 = worksheet name and cell reference
>
> something like - Monthy Totals Line 1!C$159
>
> The link should be the sum of the 2 cells, however whatever I have
> tried, excel only returns the text string, not the actual value of the
> cell from the link...
>
> I've been trying everything for 2 days and it's starting to do my head
> in... Any help would be appreciated! Cheers...
>
> --
> tastic96
> ------------------------------------------------------------------------
> tastic96's Profile: http://www.excelforum.com/member.php...o&userid=24662
> View this thread: http://www.excelforum.com/showthread...hreadid=382405

--

Dave Peterson

slight confusion, when you say "returns the text string", is this a text
string of the equation, or a concatination of the items in the cells.
if it is the equation, does it let you leave the cell?
if so, the cell is probably formated as text and you need to make sure it
is formated as general. if it is formated for text , check that there is no
space or hyphen in front of the "="
if not, it is saying there is an error in the equation. post a copy, and
see if somone can help you with the format.

I doubt it is the concatination, but if so, please post the equation.



"tastic96" wrote:

>
> Hey there, this is my first time on a forum....
>
> I need to link from a worksheet to another file. Instead of creating
> this link normally (just by opening the other file and selecting the
> desired cell), I am trying to assemble the link from a number of
> different cells in different worksheets...
>
> for example.
>
> Cell A1 in worksheet1 = long file path
>
> something like - file://nuefil08\nuefeb4\Work\Data
> Entry\31.03.05\EM22.xls
>
> Cell A1 in worksheet2 = worksheet name and cell reference
>
> something like - Monthy Totals Line 1!C$159
>
> The link should be the sum of the 2 cells, however whatever I have
> tried, excel only returns the text string, not the actual value of the
> cell from the link...
>
> I've been trying everything for 2 days and it's starting to do my head
> in... Any help would be appreciated! Cheers...
>
>
> --
> tastic96
> ------------------------------------------------------------------------
> tastic96's Profile: http://www.excelforum.com/member.php...o&userid=24662
> View this thread: http://www.excelforum.com/showthread...hreadid=382405
>
>

I think you want to use the =indirect() worksheet function.

But the bad news is that this won't work when the workbook is closed.

Harlan Grove wrote a UDF called 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

tastic96 wrote:
>
> Hey there, this is my first time on a forum....
>
> I need to link from a worksheet to another file. Instead of creating
> this link normally (just by opening the other file and selecting the
> desired cell), I am trying to assemble the link from a number of
> different cells in different worksheets...
>
> for example.
>
> Cell A1 in worksheet1 = long file path
>
> something like - file://nuefil08\nuefeb4\Work\Data
> Entry\31.03.05\EM22.xls
>
> Cell A1 in worksheet2 = worksheet name and cell reference
>
> something like - Monthy Totals Line 1!C$159
>
> The link should be the sum of the 2 cells, however whatever I have
> tried, excel only returns the text string, not the actual value of the
> cell from the link...
>
> I've been trying everything for 2 days and it's starting to do my head
> in... Any help would be appreciated! Cheers...
>
> --
> tastic96
> ------------------------------------------------------------------------
> tastic96's Profile: http://www.excelforum.com/member.php...o&userid=24662
> View this thread: http://www.excelforum.com/showthread...hreadid=382405

--

Dave Peterson

I think you want to use the =indirect() worksheet function.

But the bad news is that this won't work when the workbook is closed.

Harlan Grove wrote a UDF called 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

tastic96 wrote:
>
> Hey there, this is my first time on a forum....
>
> I need to link from a worksheet to another file. Instead of creating
> this link normally (just by opening the other file and selecting the
> desired cell), I am trying to assemble the link from a number of
> different cells in different worksheets...
>
> for example.
>
> Cell A1 in worksheet1 = long file path
>
> something like - file://nuefil08\nuefeb4\Work\Data
> Entry\31.03.05\EM22.xls
>
> Cell A1 in worksheet2 = worksheet name and cell reference
>
> something like - Monthy Totals Line 1!C$159
>
> The link should be the sum of the 2 cells, however whatever I have
> tried, excel only returns the text string, not the actual value of the
> cell from the link...
>
> I've been trying everything for 2 days and it's starting to do my head
> in... Any help would be appreciated! Cheers...
>
> --
> tastic96
> ------------------------------------------------------------------------
> tastic96's Profile: http://www.excelforum.com/member.php...o&userid=24662
> View this thread: http://www.excelforum.com/showthread...hreadid=382405

--

Dave Peterson

slight confusion, when you say "returns the text string", is this a text
string of the equation, or a concatination of the items in the cells.
if it is the equation, does it let you leave the cell?
if so, the cell is probably formated as text and you need to make sure it
is formated as general. if it is formated for text , check that there is no
space or hyphen in front of the "="
if not, it is saying there is an error in the equation. post a copy, and
see if somone can help you with the format.

I doubt it is the concatination, but if so, please post the equation.



"tastic96" wrote:

>
> Hey there, this is my first time on a forum....
>
> I need to link from a worksheet to another file. Instead of creating
> this link normally (just by opening the other file and selecting the
> desired cell), I am trying to assemble the link from a number of
> different cells in different worksheets...
>
> for example.
>
> Cell A1 in worksheet1 = long file path
>
> something like - file://nuefil08\nuefeb4\Work\Data
> Entry\31.03.05\EM22.xls
>
> Cell A1 in worksheet2 = worksheet name and cell reference
>
> something like - Monthy Totals Line 1!C$159
>
> The link should be the sum of the 2 cells, however whatever I have
> tried, excel only returns the text string, not the actual value of the
> cell from the link...
>
> I've been trying everything for 2 days and it's starting to do my head
> in... Any help would be appreciated! Cheers...
>
>
> --
> tastic96
> ------------------------------------------------------------------------
> tastic96's Profile: http://www.excelforum.com/member.php...o&userid=24662
> View this thread: http://www.excelforum.com/showthread...hreadid=382405
>
>

slight confusion, when you say "returns the text string", is this a text
string of the equation, or a concatination of the items in the cells.
if it is the equation, does it let you leave the cell?
if so, the cell is probably formated as text and you need to make sure it
is formated as general. if it is formated for text , check that there is no
space or hyphen in front of the "="
if not, it is saying there is an error in the equation. post a copy, and
see if somone can help you with the format.

I doubt it is the concatination, but if so, please post the equation.



"tastic96" wrote:

>
> Hey there, this is my first time on a forum....
>
> I need to link from a worksheet to another file. Instead of creating
> this link normally (just by opening the other file and selecting the
> desired cell), I am trying to assemble the link from a number of
> different cells in different worksheets...
>
> for example.
>
> Cell A1 in worksheet1 = long file path
>
> something like - file://nuefil08\nuefeb4\Work\Data
> Entry\31.03.05\EM22.xls
>
> Cell A1 in worksheet2 = worksheet name and cell reference
>
> something like - Monthy Totals Line 1!C$159
>
> The link should be the sum of the 2 cells, however whatever I have
> tried, excel only returns the text string, not the actual value of the
> cell from the link...
>
> I've been trying everything for 2 days and it's starting to do my head
> in... Any help would be appreciated! Cheers...
>
>
> --
> tastic96
> ------------------------------------------------------------------------
> tastic96's Profile: http://www.excelforum.com/member.php...o&userid=24662
> View this thread: http://www.excelforum.com/showthread...hreadid=382405
>
>

I think you want to use the =indirect() worksheet function.

But the bad news is that this won't work when the workbook is closed.

Harlan Grove wrote a UDF called 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

tastic96 wrote:
>
> Hey there, this is my first time on a forum....
>
> I need to link from a worksheet to another file. Instead of creating
> this link normally (just by opening the other file and selecting the
> desired cell), I am trying to assemble the link from a number of
> different cells in different worksheets...
>
> for example.
>
> Cell A1 in worksheet1 = long file path
>
> something like - file://nuefil08\nuefeb4\Work\Data
> Entry\31.03.05\EM22.xls
>
> Cell A1 in worksheet2 = worksheet name and cell reference
>
> something like - Monthy Totals Line 1!C$159
>
> The link should be the sum of the 2 cells, however whatever I have
> tried, excel only returns the text string, not the actual value of the
> cell from the link...
>
> I've been trying everything for 2 days and it's starting to do my head
> in... Any help would be appreciated! Cheers...
>
> --
> tastic96
> ------------------------------------------------------------------------
> tastic96's Profile: http://www.excelforum.com/member.php...o&userid=24662
> View this thread: http://www.excelforum.com/showthread...hreadid=382405

--

Dave Peterson

I think you want to use the =indirect() worksheet function.

But the bad news is that this won't work when the workbook is closed.

Harlan Grove wrote a UDF called 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

tastic96 wrote:
>
> Hey there, this is my first time on a forum....
>
> I need to link from a worksheet to another file. Instead of creating
> this link normally (just by opening the other file and selecting the
> desired cell), I am trying to assemble the link from a number of
> different cells in different worksheets...
>
> for example.
>
> Cell A1 in worksheet1 = long file path
>
> something like - file://nuefil08\nuefeb4\Work\Data
> Entry\31.03.05\EM22.xls
>
> Cell A1 in worksheet2 = worksheet name and cell reference
>
> something like - Monthy Totals Line 1!C$159
>
> The link should be the sum of the 2 cells, however whatever I have
> tried, excel only returns the text string, not the actual value of the
> cell from the link...
>
> I've been trying everything for 2 days and it's starting to do my head
> in... Any help would be appreciated! Cheers...
>
> --
> tastic96
> ------------------------------------------------------------------------
> tastic96's Profile: http://www.excelforum.com/member.php...o&userid=24662
> View this thread: http://www.excelforum.com/showthread...hreadid=382405

--

Dave Peterson

slight confusion, when you say "returns the text string", is this a text
string of the equation, or a concatination of the items in the cells.
if it is the equation, does it let you leave the cell?
if so, the cell is probably formated as text and you need to make sure it
is formated as general. if it is formated for text , check that there is no
space or hyphen in front of the "="
if not, it is saying there is an error in the equation. post a copy, and
see if somone can help you with the format.

I doubt it is the concatination, but if so, please post the equation.



"tastic96" wrote:

>
> Hey there, this is my first time on a forum....
>
> I need to link from a worksheet to another file. Instead of creating
> this link normally (just by opening the other file and selecting the
> desired cell), I am trying to assemble the link from a number of
> different cells in different worksheets...
>
> for example.
>
> Cell A1 in worksheet1 = long file path
>
> something like - file://nuefil08\nuefeb4\Work\Data
> Entry\31.03.05\EM22.xls
>
> Cell A1 in worksheet2 = worksheet name and cell reference
>
> something like - Monthy Totals Line 1!C$159
>
> The link should be the sum of the 2 cells, however whatever I have
> tried, excel only returns the text string, not the actual value of the
> cell from the link...
>
> I've been trying everything for 2 days and it's starting to do my head
> in... Any help would be appreciated! Cheers...
>
>
> --
> tastic96
> ------------------------------------------------------------------------
> tastic96's Profile: http://www.excelforum.com/member.php...o&userid=24662
> View this thread: http://www.excelforum.com/showthread...hreadid=382405
>
>

slight confusion, when you say "returns the text string", is this a text
string of the equation, or a concatination of the items in the cells.
if it is the equation, does it let you leave the cell?
if so, the cell is probably formated as text and you need to make sure it
is formated as general. if it is formated for text , check that there is no
space or hyphen in front of the "="
if not, it is saying there is an error in the equation. post a copy, and
see if somone can help you with the format.

I doubt it is the concatination, but if so, please post the equation.



"tastic96" wrote:

>
> Hey there, this is my first time on a forum....
>
> I need to link from a worksheet to another file. Instead of creating
> this link normally (just by opening the other file and selecting the
> desired cell), I am trying to assemble the link from a number of
> different cells in different worksheets...
>
> for example.
>
> Cell A1 in worksheet1 = long file path
>
> something like - file://nuefil08\nuefeb4\Work\Data
> Entry\31.03.05\EM22.xls
>
> Cell A1 in worksheet2 = worksheet name and cell reference
>
> something like - Monthy Totals Line 1!C$159
>
> The link should be the sum of the 2 cells, however whatever I have
> tried, excel only returns the text string, not the actual value of the
> cell from the link...
>
> I've been trying everything for 2 days and it's starting to do my head
> in... Any help would be appreciated! Cheers...
>
>
> --
> tastic96
> ------------------------------------------------------------------------
> tastic96's Profile: http://www.excelforum.com/member.php...o&userid=24662
> View this thread: http://www.excelforum.com/showthread...hreadid=382405
>
>

I think you want to use the =indirect() worksheet function.

But the bad news is that this won't work when the workbook is closed.

Harlan Grove wrote a UDF called 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

tastic96 wrote:
>
> Hey there, this is my first time on a forum....
>
> I need to link from a worksheet to another file. Instead of creating
> this link normally (just by opening the other file and selecting the
> desired cell), I am trying to assemble the link from a number of
> different cells in different worksheets...
>
> for example.
>
> Cell A1 in worksheet1 = long file path
>
> something like - file://nuefil08\nuefeb4\Work\Data
> Entry\31.03.05\EM22.xls
>
> Cell A1 in worksheet2 = worksheet name and cell reference
>
> something like - Monthy Totals Line 1!C$159
>
> The link should be the sum of the 2 cells, however whatever I have
> tried, excel only returns the text string, not the actual value of the
> cell from the link...
>
> I've been trying everything for 2 days and it's starting to do my head
> in... Any help would be appreciated! Cheers...
>
> --
> tastic96
> ------------------------------------------------------------------------
> tastic96's Profile: http://www.excelforum.com/member.php...o&userid=24662
> View this thread: http://www.excelforum.com/showthread...hreadid=382405

--

Dave Peterson

slight confusion, when you say "returns the text string", is this a text
string of the equation, or a concatination of the items in the cells.
if it is the equation, does it let you leave the cell?
if so, the cell is probably formated as text and you need to make sure it
is formated as general. if it is formated for text , check that there is no
space or hyphen in front of the "="
if not, it is saying there is an error in the equation. post a copy, and
see if somone can help you with the format.

I doubt it is the concatination, but if so, please post the equation.



"tastic96" wrote:

>
> Hey there, this is my first time on a forum....
>
> I need to link from a worksheet to another file. Instead of creating
> this link normally (just by opening the other file and selecting the
> desired cell), I am trying to assemble the link from a number of
> different cells in different worksheets...
>
> for example.
>
> Cell A1 in worksheet1 = long file path
>
> something like - file://nuefil08\nuefeb4\Work\Data
> Entry\31.03.05\EM22.xls
>
> Cell A1 in worksheet2 = worksheet name and cell reference
>
> something like - Monthy Totals Line 1!C$159
>
> The link should be the sum of the 2 cells, however whatever I have
> tried, excel only returns the text string, not the actual value of the
> cell from the link...
>
> I've been trying everything for 2 days and it's starting to do my head
> in... Any help would be appreciated! Cheers...
>
>
> --
> tastic96
> ------------------------------------------------------------------------
> tastic96's Profile: http://www.excelforum.com/member.php...o&userid=24662
> View this thread: http://www.excelforum.com/showthread...hreadid=382405
>
>

I think you want to use the =indirect() worksheet function.

But the bad news is that this won't work when the workbook is closed.

Harlan Grove wrote a UDF called 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

tastic96 wrote:
>
> Hey there, this is my first time on a forum....
>
> I need to link from a worksheet to another file. Instead of creating
> this link normally (just by opening the other file and selecting the
> desired cell), I am trying to assemble the link from a number of
> different cells in different worksheets...
>
> for example.
>
> Cell A1 in worksheet1 = long file path
>
> something like - file://nuefil08\nuefeb4\Work\Data
> Entry\31.03.05\EM22.xls
>
> Cell A1 in worksheet2 = worksheet name and cell reference
>
> something like - Monthy Totals Line 1!C$159
>
> The link should be the sum of the 2 cells, however whatever I have
> tried, excel only returns the text string, not the actual value of the
> cell from the link...
>
> I've been trying everything for 2 days and it's starting to do my head
> in... Any help would be appreciated! Cheers...
>
> --
> tastic96
> ------------------------------------------------------------------------
> tastic96's Profile: http://www.excelforum.com/member.php...o&userid=24662
> View this thread: http://www.excelforum.com/showthread...hreadid=382405

--

Dave Peterson

I think you want to use the =indirect() worksheet function.

But the bad news is that this won't work when the workbook is closed.

Harlan Grove wrote a UDF called 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

tastic96 wrote:
>
> Hey there, this is my first time on a forum....
>
> I need to link from a worksheet to another file. Instead of creating
> this link normally (just by opening the other file and selecting the
> desired cell), I am trying to assemble the link from a number of
> different cells in different worksheets...
>
> for example.
>
> Cell A1 in worksheet1 = long file path
>
> something like - file://nuefil08\nuefeb4\Work\Data
> Entry\31.03.05\EM22.xls
>
> Cell A1 in worksheet2 = worksheet name and cell reference
>
> something like - Monthy Totals Line 1!C$159
>
> The link should be the sum of the 2 cells, however whatever I have
> tried, excel only returns the text string, not the actual value of the
> cell from the link...
>
> I've been trying everything for 2 days and it's starting to do my head
> in... Any help would be appreciated! Cheers...
>
> --
> tastic96
> ------------------------------------------------------------------------
> tastic96's Profile: http://www.excelforum.com/member.php...o&userid=24662
> View this thread: http://www.excelforum.com/showthread...hreadid=382405

--

Dave Peterson

I think you want to use the =indirect() worksheet function.

But the bad news is that this won't work when the workbook is closed.

Harlan Grove wrote a UDF called 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

tastic96 wrote:
>
> Hey there, this is my first time on a forum....
>
> I need to link from a worksheet to another file. Instead of creating
> this link normally (just by opening the other file and selecting the
> desired cell), I am trying to assemble the link from a number of
> different cells in different worksheets...
>
> for example.
>
> Cell A1 in worksheet1 = long file path
>
> something like - file://nuefil08\nuefeb4\Work\Data
> Entry\31.03.05\EM22.xls
>
> Cell A1 in worksheet2 = worksheet name and cell reference
>
> something like - Monthy Totals Line 1!C$159
>
> The link should be the sum of the 2 cells, however whatever I have
> tried, excel only returns the text string, not the actual value of the
> cell from the link...
>
> I've been trying everything for 2 days and it's starting to do my head
> in... Any help would be appreciated! Cheers...
>
> --
> tastic96
> ------------------------------------------------------------------------
> tastic96's Profile: http://www.excelforum.com/member.php...o&userid=24662
> View this thread: http://www.excelforum.com/showthread...hreadid=382405

--

Dave Peterson

slight confusion, when you say "returns the text string", is this a text
string of the equation, or a concatination of the items in the cells.
if it is the equation, does it let you leave the cell?
if so, the cell is probably formated as text and you need to make sure it
is formated as general. if it is formated for text , check that there is no
space or hyphen in front of the "="
if not, it is saying there is an error in the equation. post a copy, and
see if somone can help you with the format.

I doubt it is the concatination, but if so, please post the equation.



"tastic96" wrote:

>
> Hey there, this is my first time on a forum....
>
> I need to link from a worksheet to another file. Instead of creating
> this link normally (just by opening the other file and selecting the
> desired cell), I am trying to assemble the link from a number of
> different cells in different worksheets...
>
> for example.
>
> Cell A1 in worksheet1 = long file path
>
> something like - file://nuefil08\nuefeb4\Work\Data
> Entry\31.03.05\EM22.xls
>
> Cell A1 in worksheet2 = worksheet name and cell reference
>
> something like - Monthy Totals Line 1!C$159
>
> The link should be the sum of the 2 cells, however whatever I have
> tried, excel only returns the text string, not the actual value of the
> cell from the link...
>
> I've been trying everything for 2 days and it's starting to do my head
> in... Any help would be appreciated! Cheers...
>
>
> --
> tastic96
> ------------------------------------------------------------------------
> tastic96's Profile: http://www.excelforum.com/member.php...o&userid=24662
> View this thread: http://www.excelforum.com/showthread...hreadid=382405
>
>

slight confusion, when you say "returns the text string", is this a text
string of the equation, or a concatination of the items in the cells.
if it is the equation, does it let you leave the cell?
if so, the cell is probably formated as text and you need to make sure it
is formated as general. if it is formated for text , check that there is no
space or hyphen in front of the "="
if not, it is saying there is an error in the equation. post a copy, and
see if somone can help you with the format.

I doubt it is the concatination, but if so, please post the equation.



"tastic96" wrote:

>
> Hey there, this is my first time on a forum....
>
> I need to link from a worksheet to another file. Instead of creating
> this link normally (just by opening the other file and selecting the
> desired cell), I am trying to assemble the link from a number of
> different cells in different worksheets...
>
> for example.
>
> Cell A1 in worksheet1 = long file path
>
> something like - file://nuefil08\nuefeb4\Work\Data
> Entry\31.03.05\EM22.xls
>
> Cell A1 in worksheet2 = worksheet name and cell reference
>
> something like - Monthy Totals Line 1!C$159
>
> The link should be the sum of the 2 cells, however whatever I have
> tried, excel only returns the text string, not the actual value of the
> cell from the link...
>
> I've been trying everything for 2 days and it's starting to do my head
> in... Any help would be appreciated! Cheers...
>
>
> --
> tastic96
> ------------------------------------------------------------------------
> tastic96's Profile: http://www.excelforum.com/member.php...o&userid=24662
> View this thread: http://www.excelforum.com/showthread...hreadid=382405
>
>

slight confusion, when you say "returns the text string", is this a text
string of the equation, or a concatination of the items in the cells.
if it is the equation, does it let you leave the cell?
if so, the cell is probably formated as text and you need to make sure it
is formated as general. if it is formated for text , check that there is no
space or hyphen in front of the "="
if not, it is saying there is an error in the equation. post a copy, and
see if somone can help you with the format.

I doubt it is the concatination, but if so, please post the equation.



"tastic96" wrote:

>
> Hey there, this is my first time on a forum....
>
> I need to link from a worksheet to another file. Instead of creating
> this link normally (just by opening the other file and selecting the
> desired cell), I am trying to assemble the link from a number of
> different cells in different worksheets...
>
> for example.
>
> Cell A1 in worksheet1 = long file path
>
> something like - file://nuefil08\nuefeb4\Work\Data
> Entry\31.03.05\EM22.xls
>
> Cell A1 in worksheet2 = worksheet name and cell reference
>
> something like - Monthy Totals Line 1!C$159
>
> The link should be the sum of the 2 cells, however whatever I have
> tried, excel only returns the text string, not the actual value of the
> cell from the link...
>
> I've been trying everything for 2 days and it's starting to do my head
> in... Any help would be appreciated! Cheers...
>
>
> --
> tastic96
> ------------------------------------------------------------------------
> tastic96's Profile: http://www.excelforum.com/member.php...o&userid=24662
> View this thread: http://www.excelforum.com/showthread...hreadid=382405
>
>