Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
build external ref in formula from text? | Excel Discussion (Misc queries) | |||
cell data for external reference | Excel Worksheet Functions | |||
Using cell value to reference external worksheet? | Excel Worksheet Functions | |||
How do I build a reference to an external worksheet? | Excel Worksheet Functions | |||
dynamic external cell reference | Excel Worksheet Functions |