Using cell value to build external reference
 

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.

Using cell value to build external reference
 

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



"Todd Lietha" wrote in message
...
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.

Using cell value to build external reference
 

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


"Todd Lietha" wrote:

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.

Using cell value to build external reference
 

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!


"Farhad" wrote:

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


"Todd Lietha" wrote:

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.

Using cell value to build external reference
 

=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.

Todd Lietha wrote:

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!

"Farhad" wrote:

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


"Todd Lietha" wrote:

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

Using cell value to build external reference
 

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.

"Farhad" wrote:

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


"Todd Lietha" wrote:

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.

Using cell value to build external reference
 

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.

"Farhad" wrote:

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


"Todd Lietha" wrote:

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

Using cell value to build external reference
 

I see you've posted in another forum.

I'll drop out.

Dave Peterson wrote:

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.

"Farhad" wrote:

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


"Todd Lietha" wrote:

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

--

Dave Peterson

Using cell value to build external reference
 

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?



"Dave Peterson" wrote:

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.

"Farhad" wrote:

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


"Todd Lietha" wrote:

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

Using cell value to build external reference
 

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.)


Todd Lietha wrote:

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?

"Dave Peterson" wrote:

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.

"Farhad" wrote:

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


"Todd Lietha" wrote:

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


--

Dave Peterson

Using cell value to build external reference
 

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.

"Dave Peterson" wrote:

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.)


Todd Lietha wrote:

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?

"Dave Peterson" wrote:

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.

"Farhad" wrote:

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


"Todd Lietha" wrote:

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


--

Dave Peterson

Using cell value to build external reference
 

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.)

Todd Lietha wrote:

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.

"Dave Peterson" wrote:

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.)


Todd Lietha wrote:

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?

"Dave Peterson" wrote:

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.

"Farhad" wrote:

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


"Todd Lietha" wrote:

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


--

Dave Peterson


--

Dave Peterson