Using cell value to build external reference

[Guest]

I need to use a value from a cell to buil a refernce to a cell in another
workbook. I've mostly figured out how to do it using INDIRECT but have hit
one snag.

Current:
=INDIRECT("[ProgressUpdate_"&$D$3&".xls]"&$D$3&"!D6")

where $D$3 contains the value to use in building the reference and D6 is the
cell in the other workbook that I need returned.

But, as I copy this formula down subsequent rows, I need the returned cell
to move down relatively. i.e If the above formula is in row 1 in the local
workbook and returns cell D6, when copy the formula to row 2 I need it to
return cell D7.

How can I do this?

Thanks in advance.

 

[Peo Sjoblom]

Try

=INDIRECT("[ProgressUpdate_"&$D$3&".xls]"&$D$3&"!"&CELL("address",D6))

note that if you use a sheet name with spaces in D3 your formula will throw
an error


=INDIRECT("'[ProgressUpdate_"&$D$3&".xls]"&$D$3&"'!"&CELL("address",D6))


will fix that


--


Regards,


Peo Sjoblom

 

[Guest]

Hi,

Here is another way:

=INDIRECT("[ProgressUpdate_"&$D$3&".xls]"&$D$3&"!D"&ROW()+5)

assumed you are entering this formula in the first row of your worksheet so
if it is not you have to make sure that you are making the number 6 with
ROW()+5 in your formula at the first row, for example if you are entering
this formula in the row 10 you sould put ROW-4 instead Row()+5 in the formula
above.

Thanks,

 

[Guest]

Thank you. One thing I don't understand. If I have the external workbook
referenced by [ProgressUpdate_"&$D$3&".xls] open, the reference resolves
fine. But if I don't have it open, I get a #REF error.

Any thoughts?

Thank you!

Hi,

Here is another way:

=INDIRECT("[ProgressUpdate_"&$D$3&".xls]"&$D$3&"!D"&ROW()+5)

assumed you are entering this formula in the first row of your worksheet so
if it is not you have to make sure that you are making the number 6 with
ROW()+5 in your formula at the first row, for example if you are entering
this formula in the row 10 you sould put ROW-4 instead Row()+5 in the formula
above.

Thanks,
--
Farhad Hodjat

I need to use a value from a cell to buil a refernce to a cell in another
workbook. I've mostly figured out how to do it using INDIRECT but have hit
one snag.

Current:
=INDIRECT("[ProgressUpdate_"&$D$3&".xls]"&$D$3&"!D6")

where $D$3 contains the value to use in building the reference and D6 is the
cell in the other workbook that I need returned.

But, as I copy this formula down subsequent rows, I need the returned cell
to move down relatively. i.e If the above formula is in row 1 in the local
workbook and returns cell D6, when copy the formula to row 2 I need it to
return cell D7.

How can I do this?

Thanks in advance.

 

[Dave Peterson]

=indirect() only works when the sending workbook is open.

Laurent Longre has an addin (morefunc.xll) at:
http://xcell05.free.fr/

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

Thank you. One thing I don't understand. If I have the external workbook
referenced by [ProgressUpdate_"&$D$3&".xls] open, the reference resolves
fine. But if I don't have it open, I get a #REF error.

Any thoughts?

Thank you!

Hi,

Here is another way:

=INDIRECT("[ProgressUpdate_"&$D$3&".xls]"&$D$3&"!D"&ROW()+5)

assumed you are entering this formula in the first row of your worksheet so
if it is not you have to make sure that you are making the number 6 with
ROW()+5 in your formula at the first row, for example if you are entering
this formula in the row 10 you sould put ROW-4 instead Row()+5 in the formula
above.

Thanks,
--
Farhad Hodjat

I need to use a value from a cell to buil a refernce to a cell in another
workbook. I've mostly figured out how to do it using INDIRECT but have hit
one snag.

Current:
=INDIRECT("[ProgressUpdate_"&$D$3&".xls]"&$D$3&"!D6")

where $D$3 contains the value to use in building the reference and D6 is the
cell in the other workbook that I need returned.

But, as I copy this formula down subsequent rows, I need the returned cell
to move down relatively. i.e If the above formula is in row 1 in the local
workbook and returns cell D6, when copy the formula to row 2 I need it to
return cell D7.

How can I do this?

Thanks in advance.

 

[Guest]

Since INDIRECT() requires that the external workbook is open, are you aware
of another way to build a reference to an external workbook cell without that
workbook being open?

I did try the suggested INDIRECT.EXT, but was not able to make it work. I
was trying to use INDIRECT since that was in an example that I found, but I'd
b e happy to use something else.

Hi,

Here is another way:

=INDIRECT("[ProgressUpdate_"&$D$3&".xls]"&$D$3&"!D"&ROW()+5)

assumed you are entering this formula in the first row of your worksheet so
if it is not you have to make sure that you are making the number 6 with
ROW()+5 in your formula at the first row, for example if you are entering
this formula in the row 10 you sould put ROW-4 instead Row()+5 in the formula
above.

Thanks,
--
Farhad Hodjat

I need to use a value from a cell to buil a refernce to a cell in another
workbook. I've mostly figured out how to do it using INDIRECT but have hit
one snag.

Current:
=INDIRECT("[ProgressUpdate_"&$D$3&".xls]"&$D$3&"!D6")

where $D$3 contains the value to use in building the reference and D6 is the
cell in the other workbook that I need returned.

But, as I copy this formula down subsequent rows, I need the returned cell
to move down relatively. i.e If the above formula is in row 1 in the local
workbook and returns cell D6, when copy the formula to row 2 I need it to
return cell D7.

How can I do this?

Thanks in advance.

 

[Dave Peterson]

I still don't use =indirect.ext(), but you may want to share what you tried.

In fact, you may want to try to get it working using a string--no references to
other cells at all:

=INDIRECT.ext("[ProgressUpdate_whatisind3here.xls]whatisind3again!D6")
(You really want d3 twice??)

When you get that working, you can try to build your concatenated string.

If you fail, share the string that worked. And share the values that are in D3
and any other cell you used.

Maybe someone who uses =indirect.ext() will be able to help if you give enough
info.

Since INDIRECT() requires that the external workbook is open, are you aware
of another way to build a reference to an external workbook cell without that
workbook being open?

I did try the suggested INDIRECT.EXT, but was not able to make it work. I
was trying to use INDIRECT since that was in an example that I found, but I'd
b e happy to use something else.

Hi,

Here is another way:

=INDIRECT("[ProgressUpdate_"&$D$3&".xls]"&$D$3&"!D"&ROW()+5)

assumed you are entering this formula in the first row of your worksheet so
if it is not you have to make sure that you are making the number 6 with
ROW()+5 in your formula at the first row, for example if you are entering
this formula in the row 10 you sould put ROW-4 instead Row()+5 in the formula
above.

Thanks,
--
Farhad Hodjat

I need to use a value from a cell to buil a refernce to a cell in another
workbook. I've mostly figured out how to do it using INDIRECT but have hit
one snag.

Current:
=INDIRECT("[ProgressUpdate_"&$D$3&".xls]"&$D$3&"!D6")

where $D$3 contains the value to use in building the reference and D6 is the
cell in the other workbook that I need returned.

But, as I copy this formula down subsequent rows, I need the returned cell
to move down relatively. i.e If the above formula is in row 1 in the local
workbook and returns cell D6, when copy the formula to row 2 I need it to
return cell D7.

How can I do this?

Thanks in advance.

 

[Dave Peterson]

I see you've posted in another forum.

I'll drop out.

I still don't use =indirect.ext(), but you may want to share what you tried.

In fact, you may want to try to get it working using a string--no references to
other cells at all:

=INDIRECT.ext("[ProgressUpdate_whatisind3here.xls]whatisind3again!D6")
(You really want d3 twice??)

When you get that working, you can try to build your concatenated string.

If you fail, share the string that worked. And share the values that are in D3
and any other cell you used.

Maybe someone who uses =indirect.ext() will be able to help if you give enough
info.

Since INDIRECT() requires that the external workbook is open, are you aware
of another way to build a reference to an external workbook cell without that
workbook being open?

I did try the suggested INDIRECT.EXT, but was not able to make it work. I
was trying to use INDIRECT since that was in an example that I found, but I'd
b e happy to use something else.

Hi,

Here is another way:

=INDIRECT("[ProgressUpdate_"&$D$3&".xls]"&$D$3&"!D"&ROW()+5)

assumed you are entering this formula in the first row of your worksheet so
if it is not you have to make sure that you are making the number 6 with
ROW()+5 in your formula at the first row, for example if you are entering
this formula in the row 10 you sould put ROW-4 instead Row()+5 in the formula
above.

Thanks,
--
Farhad Hodjat


:

I need to use a value from a cell to buil a refernce to a cell in another
workbook. I've mostly figured out how to do it using INDIRECT but have hit
one snag.

Current:
=INDIRECT("[ProgressUpdate_"&$D$3&".xls]"&$D$3&"!D6")

where $D$3 contains the value to use in building the reference and D6 is the
cell in the other workbook that I need returned.

But, as I copy this formula down subsequent rows, I need the returned cell
to move down relatively. i.e If the above formula is in row 1 in the local
workbook and returns cell D6, when copy the formula to row 2 I need it to
return cell D7.

How can I do this?

Thanks in advance.

 

[Guest]

Thanks for the suggestion. I had already tried that, with interesting but
unsuccessful results.

=INDIRECT.EXT([ProgressUpdate_Todd.xls]Todd'!D6)
* With the literal string, always returns a #VALUE error, regardless if the
external workbook is open or not.

=INDIRECT.EXT("[ProgressUpdate_"&$D$3&".xls]"&$D$3&"!D6")
* With the built string, returns a #VALUE error, if the external workbook is
not open. But return the correct value if the workbook is opened.


Anyone with INDIRECT.EXT experience out there?

I still don't use =indirect.ext(), but you may want to share what you tried.

In fact, you may want to try to get it working using a string--no references to
other cells at all:

=INDIRECT.ext("[ProgressUpdate_whatisind3here.xls]whatisind3again!D6")
(You really want d3 twice??)

When you get that working, you can try to build your concatenated string.

If you fail, share the string that worked. And share the values that are in D3
and any other cell you used.

Maybe someone who uses =indirect.ext() will be able to help if you give enough
info.

Since INDIRECT() requires that the external workbook is open, are you aware
of another way to build a reference to an external workbook cell without that
workbook being open?

I did try the suggested INDIRECT.EXT, but was not able to make it work. I
was trying to use INDIRECT since that was in an example that I found, but I'd
b e happy to use something else.

Hi,

Here is another way:

=INDIRECT("[ProgressUpdate_"&$D$3&".xls]"&$D$3&"!D"&ROW()+5)

assumed you are entering this formula in the first row of your worksheet so
if it is not you have to make sure that you are making the number 6 with
ROW()+5 in your formula at the first row, for example if you are entering
this formula in the row 10 you sould put ROW-4 instead Row()+5 in the formula
above.

Thanks,
--
Farhad Hodjat


:

I need to use a value from a cell to buil a refernce to a cell in another
workbook. I've mostly figured out how to do it using INDIRECT but have hit
one snag.

Current:
=INDIRECT("[ProgressUpdate_"&$D$3&".xls]"&$D$3&"!D6")

where $D$3 contains the value to use in building the reference and D6 is the
cell in the other workbook that I need returned.

But, as I copy this formula down subsequent rows, I need the returned cell
to move down relatively. i.e If the above formula is in row 1 in the local
workbook and returns cell D6, when copy the formula to row 2 I need it to
return cell D7.

How can I do this?

Thanks in advance.

 

[Dave Peterson]

This formula won't work:
=INDIRECT.EXT([ProgressUpdate_Todd.xls]Todd'!D6)

You're missing an apostrophe and you didn't put the string in quotes.

I'd try this first:
=INDIRECT.EXT("'[ProgressUpdate_Todd.xls]Todd'!D6")

And work toward:
=INDIRECT.EXT("'[ProgressUpdate_" & d3 & "]" & d3 & "'!D6")

(still untested.)

Thanks for the suggestion. I had already tried that, with interesting but
unsuccessful results.

=INDIRECT.EXT([ProgressUpdate_Todd.xls]Todd'!D6)
* With the literal string, always returns a #VALUE error, regardless if the
external workbook is open or not.

=INDIRECT.EXT("[ProgressUpdate_"&$D$3&".xls]"&$D$3&"!D6")
* With the built string, returns a #VALUE error, if the external workbook is
not open. But return the correct value if the workbook is opened.

Anyone with INDIRECT.EXT experience out there?

I still don't use =indirect.ext(), but you may want to share what you tried.

In fact, you may want to try to get it working using a string--no references to
other cells at all:

=INDIRECT.ext("[ProgressUpdate_whatisind3here.xls]whatisind3again!D6")
(You really want d3 twice??)

When you get that working, you can try to build your concatenated string.

If you fail, share the string that worked. And share the values that are in D3
and any other cell you used.

Maybe someone who uses =indirect.ext() will be able to help if you give enough
info.

Since INDIRECT() requires that the external workbook is open, are you aware
of another way to build a reference to an external workbook cell without that
workbook being open?

I did try the suggested INDIRECT.EXT, but was not able to make it work. I
was trying to use INDIRECT since that was in an example that I found, but I'd
b e happy to use something else.

:

Hi,

Here is another way:

=INDIRECT("[ProgressUpdate_"&$D$3&".xls]"&$D$3&"!D"&ROW()+5)

assumed you are entering this formula in the first row of your worksheet so
if it is not you have to make sure that you are making the number 6 with
ROW()+5 in your formula at the first row, for example if you are entering
this formula in the row 10 you sould put ROW-4 instead Row()+5 in the formula
above.

Thanks,
--
Farhad Hodjat


:

I need to use a value from a cell to buil a refernce to a cell in another
workbook. I've mostly figured out how to do it using INDIRECT but have hit
one snag.

Current:
=INDIRECT("[ProgressUpdate_"&$D$3&".xls]"&$D$3&"!D6")

where $D$3 contains the value to use in building the reference and D6 is the
cell in the other workbook that I need returned.

But, as I copy this formula down subsequent rows, I need the returned cell
to move down relatively. i.e If the above formula is in row 1 in the local
workbook and returns cell D6, when copy the formula to row 2 I need it to
return cell D7.

How can I do this?

Thanks in advance.

 

[Guest]

Dave - Thank you very much for the help! The errant tick in my example was a
typo - wasn't realy there int he spreadsheet. But it led me to try including
both single and double quotes in teh string that was being constructed and it
worked! I don't know why the single quotes are necessary as the string has
no spaces, but it works now.

Thank you again.

This formula won't work:
=INDIRECT.EXT([ProgressUpdate_Todd.xls]Todd'!D6)

You're missing an apostrophe and you didn't put the string in quotes.

I'd try this first:
=INDIRECT.EXT("'[ProgressUpdate_Todd.xls]Todd'!D6")

And work toward:
=INDIRECT.EXT("'[ProgressUpdate_" & d3 & "]" & d3 & "'!D6")

(still untested.)

Thanks for the suggestion. I had already tried that, with interesting but
unsuccessful results.

=INDIRECT.EXT([ProgressUpdate_Todd.xls]Todd'!D6)
* With the literal string, always returns a #VALUE error, regardless if the
external workbook is open or not.

=INDIRECT.EXT("[ProgressUpdate_"&$D$3&".xls]"&$D$3&"!D6")
* With the built string, returns a #VALUE error, if the external workbook is
not open. But return the correct value if the workbook is opened.

Anyone with INDIRECT.EXT experience out there?

I still don't use =indirect.ext(), but you may want to share what you tried.

In fact, you may want to try to get it working using a string--no references to
other cells at all:

=INDIRECT.ext("[ProgressUpdate_whatisind3here.xls]whatisind3again!D6")
(You really want d3 twice??)

When you get that working, you can try to build your concatenated string.

If you fail, share the string that worked. And share the values that are in D3
and any other cell you used.

Maybe someone who uses =indirect.ext() will be able to help if you give enough
info.

Todd Lietha wrote:

Since INDIRECT() requires that the external workbook is open, are you aware
of another way to build a reference to an external workbook cell without that
workbook being open?

I did try the suggested INDIRECT.EXT, but was not able to make it work. I
was trying to use INDIRECT since that was in an example that I found, but I'd
b e happy to use something else.

:

Hi,

Here is another way:

=INDIRECT("[ProgressUpdate_"&$D$3&".xls]"&$D$3&"!D"&ROW()+5)

assumed you are entering this formula in the first row of your worksheet so
if it is not you have to make sure that you are making the number 6 with
ROW()+5 in your formula at the first row, for example if you are entering
this formula in the row 10 you sould put ROW-4 instead Row()+5 in the formula
above.

Thanks,
--
Farhad Hodjat


:

I need to use a value from a cell to buil a refernce to a cell in another
workbook. I've mostly figured out how to do it using INDIRECT but have hit
one snag.

Current:
=INDIRECT("[ProgressUpdate_"&$D$3&".xls]"&$D$3&"!D6")

where $D$3 contains the value to use in building the reference and D6 is the
cell in the other workbook that I need returned.

But, as I copy this formula down subsequent rows, I need the returned cell
to move down relatively. i.e If the above formula is in row 1 in the local
workbook and returns cell D6, when copy the formula to row 2 I need it to
return cell D7.

How can I do this?

Thanks in advance.

 

[Dave Peterson]

You may not need the apostrophes--but if you put them in, it won't hurt.

And if you need them and you don't include them, then your formula will break.

(And it's just not spaces that requires the apostrophes.)

Dave - Thank you very much for the help! The errant tick in my example was a
typo - wasn't realy there int he spreadsheet. But it led me to try including
both single and double quotes in teh string that was being constructed and it
worked! I don't know why the single quotes are necessary as the string has
no spaces, but it works now.

Thank you again.

This formula won't work:
=INDIRECT.EXT([ProgressUpdate_Todd.xls]Todd'!D6)

You're missing an apostrophe and you didn't put the string in quotes.

I'd try this first:
=INDIRECT.EXT("'[ProgressUpdate_Todd.xls]Todd'!D6")

And work toward:
=INDIRECT.EXT("'[ProgressUpdate_" & d3 & "]" & d3 & "'!D6")

(still untested.)

Thanks for the suggestion. I had already tried that, with interesting but
unsuccessful results.

=INDIRECT.EXT([ProgressUpdate_Todd.xls]Todd'!D6)
* With the literal string, always returns a #VALUE error, regardless if the
external workbook is open or not.

=INDIRECT.EXT("[ProgressUpdate_"&$D$3&".xls]"&$D$3&"!D6")
* With the built string, returns a #VALUE error, if the external workbook is
not open. But return the correct value if the workbook is opened.

Anyone with INDIRECT.EXT experience out there?

:

I still don't use =indirect.ext(), but you may want to share what you tried.

In fact, you may want to try to get it working using a string--no references to
other cells at all:

=INDIRECT.ext("[ProgressUpdate_whatisind3here.xls]whatisind3again!D6")
(You really want d3 twice??)

When you get that working, you can try to build your concatenated string.

If you fail, share the string that worked. And share the values that are in D3
and any other cell you used.

Maybe someone who uses =indirect.ext() will be able to help if you give enough
info.

Todd Lietha wrote:

Since INDIRECT() requires that the external workbook is open, are you aware
of another way to build a reference to an external workbook cell without that
workbook being open?

I did try the suggested INDIRECT.EXT, but was not able to make it work. I
was trying to use INDIRECT since that was in an example that I found, but I'd
b e happy to use something else.

:

Hi,

Here is another way:

=INDIRECT("[ProgressUpdate_"&$D$3&".xls]"&$D$3&"!D"&ROW()+5)

assumed you are entering this formula in the first row of your worksheet so
if it is not you have to make sure that you are making the number 6 with
ROW()+5 in your formula at the first row, for example if you are entering
this formula in the row 10 you sould put ROW-4 instead Row()+5 in the formula
above.

Thanks,
--
Farhad Hodjat


:

I need to use a value from a cell to buil a refernce to a cell in another
workbook. I've mostly figured out how to do it using INDIRECT but have hit
one snag.

Current:
=INDIRECT("[ProgressUpdate_"&$D$3&".xls]"&$D$3&"!D6")

where $D$3 contains the value to use in building the reference and D6 is the
cell in the other workbook that I need returned.

But, as I copy this formula down subsequent rows, I need the returned cell
to move down relatively. i.e If the above formula is in row 1 in the local
workbook and returns cell D6, when copy the formula to row 2 I need it to
return cell D7.

How can I do this?

Thanks in advance.