Insert a cell value into a link to another worksheet.

[bsharp]

I have a summary workbook that pulls information in from other workbooks.
Each workbook relates to one specific real estate property. So each of the
source workbooks is exactly the same, except for the values within the cells
within the workbook, but the format and headings are identical.

So in my summary document, I have the property addresses down the far left
column, then I put links to the specific cell within the source document
across the row. For example: \\computer\my files\123 main st\[123 main
st.xlsx]termsA1

It looks like this -

PROPERTY PRICE CLOSE DATE BUYER NAME
123 main st $200000 5/1/09 Smith
456 maple $100000 5/15/09 Jones


What I would like to do is just type in the property address into the left
column to start a new row, and the links will use that address to insert into
the path. So in my path example above, I would like to insert a cell
reference instead of the words "123 main st"

I'm using find and replace now, but was hoping for a more elegant solution.

 

[Sheeloo]

Try INDIRECT...

essentially build a string using a formula which gives you the address of
the cell you are pulling the value from and wrap INDIRECT around

eg
=Sheet1!A1
can be replaced by
=INDIRECT("Sheet1!A" & B1)
where B1 contains 1

when B1 changes to 2
your formula will work as
=Sheet1!A2

 

[Harlan Grove]

Try INDIRECT... ....
eg
=Sheet1!A1
can be replaced by
=INDIRECT("Sheet1!A" & B1)
where B1 contains 1

....

Or avoid inefficient volatile functions and use

=INDEX(Sheet1!$A:$A,B1)

 

[Dave Peterson]

The function you'd want to use that's built into excel is =indirect(). But that
function returns an error if the sending workbook is closed.

If that's a problem, then Laurent Longre has an addin (morefunc.xll) at:
http://xcell05.free.fr/
or
http://xcell05.free.fr/morefunc/english/index.htm

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

I have a summary workbook that pulls information in from other workbooks.
Each workbook relates to one specific real estate property. So each of the
source workbooks is exactly the same, except for the values within the cells
within the workbook, but the format and headings are identical.

So in my summary document, I have the property addresses down the far left
column, then I put links to the specific cell within the source document
across the row. For example: \\computer\my files\123 main st\[123 main
st.xlsx]termsA1

It looks like this -

PROPERTY PRICE CLOSE DATE BUYER NAME
123 main st $200000 5/1/09 Smith
456 maple $100000 5/15/09 Jones

What I would like to do is just type in the property address into the left
column to start a new row, and the links will use that address to insert into
the path. So in my path example above, I would like to insert a cell
reference instead of the words "123 main st"

I'm using find and replace now, but was hoping for a more elegant solution.

 

[bsharp]

I've tried indirect before, but I must be missing something.

Remember that I am trying to insert this WITHIN the path to the file. Here
is my actual formula -

='\\Forms\Pending\address\[address.xlsx]importexport'!G$2)

I want it to replace the word "address" in two spots above with the contents
of cell A7. Can you give me the syntax of exactly how to type it within that
formula? I tried putting in indirect as follows, but it didn't work.

='\\Forms\Pending\=indirect("Sheet1"&a7)\[=indirect("Sheet1"&a7).xlsx]importexport'!g$2)

Try INDIRECT...

essentially build a string using a formula which gives you the address of
the cell you are pulling the value from and wrap INDIRECT around

eg
=Sheet1!A1
can be replaced by
=INDIRECT("Sheet1!A" & B1)
where B1 contains 1

when B1 changes to 2
your formula will work as
=Sheet1!A2

I have a summary workbook that pulls information in from other workbooks.
Each workbook relates to one specific real estate property. So each of the
source workbooks is exactly the same, except for the values within the cells
within the workbook, but the format and headings are identical.

So in my summary document, I have the property addresses down the far left
column, then I put links to the specific cell within the source document
across the row. For example: \\computer\my files\123 main st\[123 main
st.xlsx]termsA1

It looks like this -

PROPERTY PRICE CLOSE DATE BUYER NAME
123 main st $200000 5/1/09 Smith
456 maple $100000 5/15/09 Jones


What I would like to do is just type in the property address into the left
column to start a new row, and the links will use that address to insert into
the path. So in my path example above, I would like to insert a cell
reference instead of the words "123 main st"

I'm using find and replace now, but was hoping for a more elegant solution.

 

[bsharp]

Thanks, but how do I insert that into the file path so excel recognizes that
as a function and not as just more text?

For example - \\computer\my files\123 main st\[123 main st.xlsx]sheet1a1

What exactly do I replace "123 main st" with if I want it to look in cell A7?

 

[Sheeloo]

Try
=INDIRECT("'\\Forms\Pending\" & A7 & "\[" & A7 & ".xlsx]importexport'!G$2")

If the following works then the above will also work
'\\Forms\Pending\FILENAME\[FILENAME.xlsx]importexport'!G$2

if A7 contains FILENAME

I've tried indirect before, but I must be missing something.

Remember that I am trying to insert this WITHIN the path to the file. Here
is my actual formula -

='\\Forms\Pending\address\[address.xlsx]importexport'!G$2)

I want it to replace the word "address" in two spots above with the contents
of cell A7. Can you give me the syntax of exactly how to type it within that
formula? I tried putting in indirect as follows, but it didn't work.

='\\Forms\Pending\=indirect("Sheet1"&a7)\[=indirect("Sheet1"&a7).xlsx]importexport'!g$2)

Try INDIRECT...

essentially build a string using a formula which gives you the address of
the cell you are pulling the value from and wrap INDIRECT around

eg
=Sheet1!A1
can be replaced by
=INDIRECT("Sheet1!A" & B1)
where B1 contains 1

when B1 changes to 2
your formula will work as
=Sheet1!A2

I have a summary workbook that pulls information in from other workbooks.
Each workbook relates to one specific real estate property. So each of the
source workbooks is exactly the same, except for the values within the cells
within the workbook, but the format and headings are identical.

So in my summary document, I have the property addresses down the far left
column, then I put links to the specific cell within the source document
across the row. For example: \\computer\my files\123 main st\[123 main
st.xlsx]termsA1

It looks like this -

PROPERTY PRICE CLOSE DATE BUYER NAME
123 main st $200000 5/1/09 Smith
456 maple $100000 5/15/09 Jones


What I would like to do is just type in the property address into the left
column to start a new row, and the links will use that address to insert into
the path. So in my path example above, I would like to insert a cell
reference instead of the words "123 main st"

I'm using find and replace now, but was hoping for a more elegant solution.

 

[bsharp]

Thanks, now that makes sense, and that does work when I have the source
workbook open. But I want to summarize many, many workbooks without having to
have them all open. And even if I did open them all up, once I close them the
data goes away.

I would like to be able to insert the contents of the cell into that path
WITHOUT using indirect?

Try
=INDIRECT("'\\Forms\Pending\" & A7 & "\[" & A7 & ".xlsx]importexport'!G$2")

If the following works then the above will also work
'\\Forms\Pending\FILENAME\[FILENAME.xlsx]importexport'!G$2

if A7 contains FILENAME

I've tried indirect before, but I must be missing something.

Remember that I am trying to insert this WITHIN the path to the file. Here
is my actual formula -

='\\Forms\Pending\address\[address.xlsx]importexport'!G$2)

I want it to replace the word "address" in two spots above with the contents
of cell A7. Can you give me the syntax of exactly how to type it within that
formula? I tried putting in indirect as follows, but it didn't work.

='\\Forms\Pending\=indirect("Sheet1"&a7)\[=indirect("Sheet1"&a7).xlsx]importexport'!g$2)

Try INDIRECT...

essentially build a string using a formula which gives you the address of
the cell you are pulling the value from and wrap INDIRECT around

eg
=Sheet1!A1
can be replaced by
=INDIRECT("Sheet1!A" & B1)
where B1 contains 1

when B1 changes to 2
your formula will work as
=Sheet1!A2

:

I have a summary workbook that pulls information in from other workbooks.
Each workbook relates to one specific real estate property. So each of the
source workbooks is exactly the same, except for the values within the cells
within the workbook, but the format and headings are identical.

So in my summary document, I have the property addresses down the far left
column, then I put links to the specific cell within the source document
across the row. For example: \\computer\my files\123 main st\[123 main
st.xlsx]termsA1

It looks like this -

PROPERTY PRICE CLOSE DATE BUYER NAME
123 main st $200000 5/1/09 Smith
456 maple $100000 5/15/09 Jones


What I would like to do is just type in the property address into the left
column to start a new row, and the links will use that address to insert into
the path. So in my path example above, I would like to insert a cell
reference instead of the words "123 main st"

I'm using find and replace now, but was hoping for a more elegant solution.

 

[Sheeloo]

Yes, that is a limitation of INDIRECT

I have not tested his but try this
create a name (INSERT->NAME->DEFINE
and put the INDIRECT function
=INDIRECT("'\\Forms\Pending\" & A7 & "\[" & A7 & ".xlsx]importexport'!G$2")
in the REFERS TO box...

Let us know how it goes

Thanks, now that makes sense, and that does work when I have the source
workbook open. But I want to summarize many, many workbooks without having to
have them all open. And even if I did open them all up, once I close them the
data goes away.

I would like to be able to insert the contents of the cell into that path
WITHOUT using indirect?

Try
=INDIRECT("'\\Forms\Pending\" & A7 & "\[" & A7 & ".xlsx]importexport'!G$2")

If the following works then the above will also work
'\\Forms\Pending\FILENAME\[FILENAME.xlsx]importexport'!G$2

if A7 contains FILENAME

I've tried indirect before, but I must be missing something.

Remember that I am trying to insert this WITHIN the path to the file. Here
is my actual formula -

='\\Forms\Pending\address\[address.xlsx]importexport'!G$2)

I want it to replace the word "address" in two spots above with the contents
of cell A7. Can you give me the syntax of exactly how to type it within that
formula? I tried putting in indirect as follows, but it didn't work.

='\\Forms\Pending\=indirect("Sheet1"&a7)\[=indirect("Sheet1"&a7).xlsx]importexport'!g$2)


:

Try INDIRECT...

essentially build a string using a formula which gives you the address of
the cell you are pulling the value from and wrap INDIRECT around

eg
=Sheet1!A1
can be replaced by
=INDIRECT("Sheet1!A" & B1)
where B1 contains 1

when B1 changes to 2
your formula will work as
=Sheet1!A2

:

I have a summary workbook that pulls information in from other workbooks.
Each workbook relates to one specific real estate property. So each of the
source workbooks is exactly the same, except for the values within the cells
within the workbook, but the format and headings are identical.

So in my summary document, I have the property addresses down the far left
column, then I put links to the specific cell within the source document
across the row. For example: \\computer\my files\123 main st\[123 main
st.xlsx]termsA1

It looks like this -

PROPERTY PRICE CLOSE DATE BUYER NAME
123 main st $200000 5/1/09 Smith
456 maple $100000 5/15/09 Jones


What I would like to do is just type in the property address into the left
column to start a new row, and the links will use that address to insert into
the path. So in my path example above, I would like to insert a cell
reference instead of the words "123 main st"

I'm using find and replace now, but was hoping for a more elegant solution.

 

[Harlan Grove]

Thanks, but how do I insert that into the file path so excel recognizes that
as a function and not as just more text?

....

Sorry, I misunderstood.

See

http://groups.google.com/group/microsoft.public.excel.worksheet.functions/msg/ac443753560f0075

 

[bsharp]

Thanks, but that won't work, either. I'd have to have names for each workbook
I want to source, and then for each cell within that worksheet.

I appreciate your efforts, thanks for trying.

For now I'll just do find and replace until some other solution arises.

I may have to just use indirect, and then try to add-in that Dave Peterson
posted. I was hoping to avoid that because I want to share this with others
in my office, and was trying to keep it as simple as possible. I assumed
they'd have to download that add-in, too, to make it work on their computers.

Brian

Yes, that is a limitation of INDIRECT

I have not tested his but try this
create a name (INSERT->NAME->DEFINE
and put the INDIRECT function
=INDIRECT("'\\Forms\Pending\" & A7 & "\[" & A7 & ".xlsx]importexport'!G$2")
in the REFERS TO box...

Let us know how it goes

Thanks, now that makes sense, and that does work when I have the source
workbook open. But I want to summarize many, many workbooks without having to
have them all open. And even if I did open them all up, once I close them the
data goes away.

I would like to be able to insert the contents of the cell into that path
WITHOUT using indirect?

Try
=INDIRECT("'\\Forms\Pending\" & A7 & "\[" & A7 & ".xlsx]importexport'!G$2")

If the following works then the above will also work
'\\Forms\Pending\FILENAME\[FILENAME.xlsx]importexport'!G$2

if A7 contains FILENAME

:

I've tried indirect before, but I must be missing something.

Remember that I am trying to insert this WITHIN the path to the file. Here
is my actual formula -

='\\Forms\Pending\address\[address.xlsx]importexport'!G$2)

I want it to replace the word "address" in two spots above with the contents
of cell A7. Can you give me the syntax of exactly how to type it within that
formula? I tried putting in indirect as follows, but it didn't work.

='\\Forms\Pending\=indirect("Sheet1"&a7)\[=indirect("Sheet1"&a7).xlsx]importexport'!g$2)


:

Try INDIRECT...

essentially build a string using a formula which gives you the address of
the cell you are pulling the value from and wrap INDIRECT around

eg
=Sheet1!A1
can be replaced by
=INDIRECT("Sheet1!A" & B1)
where B1 contains 1

when B1 changes to 2
your formula will work as
=Sheet1!A2

:

I have a summary workbook that pulls information in from other workbooks.
Each workbook relates to one specific real estate property. So each of the
source workbooks is exactly the same, except for the values within the cells
within the workbook, but the format and headings are identical.

So in my summary document, I have the property addresses down the far left
column, then I put links to the specific cell within the source document
across the row. For example: \\computer\my files\123 main st\[123 main
st.xlsx]termsA1

It looks like this -

PROPERTY PRICE CLOSE DATE BUYER NAME
123 main st $200000 5/1/09 Smith
456 maple $100000 5/15/09 Jones


What I would like to do is just type in the property address into the left
column to start a new row, and the links will use that address to insert into
the path. So in my path example above, I would like to insert a cell
reference instead of the words "123 main st"

I'm using find and replace now, but was hoping for a more elegant solution.