Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Indirect - Why does it seem to only work sometimes?
Two situations: 1) Create two new workbooks 2) In one workbook (Workbook1) enter value of 200 in cell A1 3) In second workbook (Workbook2) enter [Workbook1]Sheet1!A1 into cell A1 4) In cell A2 in second workbook, enter =Indirect(A1) 5) I get back 200 Situation 2 1) I have two large workbooks 2) In one workbook, called [Award Stats], in worksheet 2006 Stats, in cell B304, I have value of 2,000,000 3) In another workbook, in cell B42, I have the following: '[Award Stats]2006 Stats'!B304 4) In cell B43 in second workbook, I have =Indirect(B42) 5) I get back #REF! Why the difference? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Indirect - Why does it seem to only work sometimes?
4) In cell B43 in second workbook, I have =Indirect(B42)
Try instead in B43: =INDIRECT("'"&B42) Looks like the preceding single quote in the text entered in B42 was "swallowed" by Excel, re your line: 3) In another workbook, in cell B42, I have the following: '[Award Stats]2006 Stats'!B304 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- " wrote: Two situations: 1) Create two new workbooks 2) In one workbook (Workbook1) enter value of 200 in cell A1 3) In second workbook (Workbook2) enter [Workbook1]Sheet1!A1 into cell A1 4) In cell A2 in second workbook, enter =Indirect(A1) 5) I get back 200 Situation 2 1) I have two large workbooks 2) In one workbook, called [Award Stats], in worksheet 2006 Stats, in cell B304, I have value of 2,000,000 3) In another workbook, in cell B42, I have the following: '[Award Stats]2006 Stats'!B304 4) In cell B43 in second workbook, I have =Indirect(B42) 5) I get back #REF! Why the difference? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Indirect - Why does it seem to only work sometimes?
It gets hung up on the apostrophe since it is both part of workbook/sheet
naming and also a text precedent, remove the leading apostrophe in B42 like this [Award Stats.xls]Stats'!B304 then in B43 use =INDIRECT("'"&B42) also note that if you close the source workbook indirect will not work -- Regards, Peo Sjoblom Excel 95 - Excel 2007 Northwest Excel Solutions www.nwexcelsolutions.com "It is a good thing to follow the first law of holes; if you are in one stop digging." Lord Healey wrote in message oups.com... Two situations: 1) Create two new workbooks 2) In one workbook (Workbook1) enter value of 200 in cell A1 3) In second workbook (Workbook2) enter [Workbook1]Sheet1!A1 into cell A1 4) In cell A2 in second workbook, enter =Indirect(A1) 5) I get back 200 Situation 2 1) I have two large workbooks 2) In one workbook, called [Award Stats], in worksheet 2006 Stats, in cell B304, I have value of 2,000,000 3) In another workbook, in cell B42, I have the following: '[Award Stats]2006 Stats'!B304 4) In cell B43 in second workbook, I have =Indirect(B42) 5) I get back #REF! Why the difference? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Indirect - Why does it seem to only work sometimes?
Actually you don't have to remove the leading apostrophe in B42
-- Regards, Peo Sjoblom Excel 95 - Excel 2007 Northwest Excel Solutions www.nwexcelsolutions.com "It is a good thing to follow the first law of holes; if you are in one stop digging." Lord Healey "Peo Sjoblom" wrote in message ... It gets hung up on the apostrophe since it is both part of workbook/sheet naming and also a text precedent, remove the leading apostrophe in B42 like this [Award Stats.xls]Stats'!B304 then in B43 use =INDIRECT("'"&B42) also note that if you close the source workbook indirect will not work -- Regards, Peo Sjoblom Excel 95 - Excel 2007 Northwest Excel Solutions www.nwexcelsolutions.com "It is a good thing to follow the first law of holes; if you are in one stop digging." Lord Healey wrote in message oups.com... Two situations: 1) Create two new workbooks 2) In one workbook (Workbook1) enter value of 200 in cell A1 3) In second workbook (Workbook2) enter [Workbook1]Sheet1!A1 into cell A1 4) In cell A2 in second workbook, enter =Indirect(A1) 5) I get back 200 Situation 2 1) I have two large workbooks 2) In one workbook, called [Award Stats], in worksheet 2006 Stats, in cell B304, I have value of 2,000,000 3) In another workbook, in cell B42, I have the following: '[Award Stats]2006 Stats'!B304 4) In cell B43 in second workbook, I have =Indirect(B42) 5) I get back #REF! Why the difference? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Indirect - Why does it seem to only work sometimes?
Without actually testing, perhaps what you see is different that what
you enter. The initial single quote would indicate a text entry and would not be seen in the cell. Try with two single quotes at the beginning. Actually I did just test this and it bears out my reply. the workbook is 2004-11.xls so entire test can be done with same workbook D8: ''[2004-11.xls]sheet146'!f11 D9: =INDIRECT(D8) F11: 'F11text --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm wrote in message oups.com... Two situations: 1) Create two new workbooks 2) In one workbook (Workbook1) enter value of 200 in cell A1 3) In second workbook (Workbook2) enter [Workbook1]Sheet1!A1 into cell A1 4) In cell A2 in second workbook, enter =Indirect(A1) 5) I get back 200 Situation 2 1) I have two large workbooks 2) In one workbook, called [Award Stats], in worksheet 2006 Stats, in cell B304, I have value of 2,000,000 3) In another workbook, in cell B42, I have the following: '[Award Stats]2006 Stats'!B304 4) In cell B43 in second workbook, I have =Indirect(B42) 5) I get back #REF! Why the difference? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Indirect - Why does it seem to only work sometimes?
Peo Sjoblom wrote: Actually you don't have to remove the leading apostrophe in B42 -- Regards, Peo Sjoblom Excel 95 - Excel 2007 Northwest Excel Solutions www.nwexcelsolutions.com "It is a good thing to follow the first law of holes; if you are in one stop digging." Lord Healey "Peo Sjoblom" wrote in message ... It gets hung up on the apostrophe since it is both part of workbook/sheet naming and also a text precedent, remove the leading apostrophe in B42 like this [Award Stats.xls]Stats'!B304 then in B43 use =INDIRECT("'"&B42) also note that if you close the source workbook indirect will not work -- Regards, Peo Sjoblom Excel 95 - Excel 2007 Northwest Excel Solutions www.nwexcelsolutions.com "It is a good thing to follow the first law of holes; if you are in one stop digging." Lord Healey wrote in message oups.com... Two situations: 1) Create two new workbooks 2) In one workbook (Workbook1) enter value of 200 in cell A1 3) In second workbook (Workbook2) enter [Workbook1]Sheet1!A1 into cell A1 4) In cell A2 in second workbook, enter =Indirect(A1) 5) I get back 200 Situation 2 1) I have two large workbooks 2) In one workbook, called [Award Stats], in worksheet 2006 Stats, in cell B304, I have value of 2,000,000 3) In another workbook, in cell B42, I have the following: '[Award Stats]2006 Stats'!B304 4) In cell B43 in second workbook, I have =Indirect(B42) 5) I get back #REF! Why the difference? Thanks - is there no way to do this w/o having the other worksheet open? The idea here is for a user to type in (or select from a validated list) the name of a file. The file will always be of a similar format (2006 Stats, 2005 Stats, etc.). In some cases it would be monthly rather than annually - not that that matters. I'd want to limit the user's work to just picking the file name w/o having to open all the other files as that sort of defeats the purpose. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Indirect - Why does it seem to only work sometimes?
Hi
I myself sometimes use some standard name for cases, where data are imported from various source files. I.e. The source file is renamed to standard one, data are imported from it to another workbook, then another source file is renamed, etc. The another way is to write a procedure, which asks for source file (File Open Dialog), and then either rewrites all links ( using ReplaceAll) on sheet(s), or imports data from selected source file into workbook. There was some link to download an UDF working like INDERECT, but with closed source file too, in some of Excel NG's some time ago. Arvi Laanemets wrote in message ups.com... Peo Sjoblom wrote: Actually you don't have to remove the leading apostrophe in B42 -- Regards, Peo Sjoblom Excel 95 - Excel 2007 Northwest Excel Solutions www.nwexcelsolutions.com "It is a good thing to follow the first law of holes; if you are in one stop digging." Lord Healey "Peo Sjoblom" wrote in message ... It gets hung up on the apostrophe since it is both part of workbook/sheet naming and also a text precedent, remove the leading apostrophe in B42 like this [Award Stats.xls]Stats'!B304 then in B43 use =INDIRECT("'"&B42) also note that if you close the source workbook indirect will not work -- Regards, Peo Sjoblom Excel 95 - Excel 2007 Northwest Excel Solutions www.nwexcelsolutions.com "It is a good thing to follow the first law of holes; if you are in one stop digging." Lord Healey wrote in message oups.com... Two situations: 1) Create two new workbooks 2) In one workbook (Workbook1) enter value of 200 in cell A1 3) In second workbook (Workbook2) enter [Workbook1]Sheet1!A1 into cell A1 4) In cell A2 in second workbook, enter =Indirect(A1) 5) I get back 200 Situation 2 1) I have two large workbooks 2) In one workbook, called [Award Stats], in worksheet 2006 Stats, in cell B304, I have value of 2,000,000 3) In another workbook, in cell B42, I have the following: '[Award Stats]2006 Stats'!B304 4) In cell B43 in second workbook, I have =Indirect(B42) 5) I get back #REF! Why the difference? Thanks - is there no way to do this w/o having the other worksheet open? The idea here is for a user to type in (or select from a validated list) the name of a file. The file will always be of a similar format (2006 Stats, 2005 Stats, etc.). In some cases it would be monthly rather than annually - not that that matters. I'd want to limit the user's work to just picking the file name w/o having to open all the other files as that sort of defeats the purpose. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Indirect - Why does it seem to only work sometimes?
You would need an add-in, the easiest would probably be to download and
install Laurent Longre's Morefunc http://xcell05.free.fr/english/ INDIRECT.EXT will work with closed workbooks -- Regards, Peo Sjoblom Excel 95 - Excel 2007 Northwest Excel Solutions www.nwexcelsolutions.com "It is a good thing to follow the first law of holes; if you are in one stop digging." Lord Healey wrote in message ups.com... Peo Sjoblom wrote: Actually you don't have to remove the leading apostrophe in B42 -- Regards, Peo Sjoblom Excel 95 - Excel 2007 Northwest Excel Solutions www.nwexcelsolutions.com "It is a good thing to follow the first law of holes; if you are in one stop digging." Lord Healey "Peo Sjoblom" wrote in message ... It gets hung up on the apostrophe since it is both part of workbook/sheet naming and also a text precedent, remove the leading apostrophe in B42 like this [Award Stats.xls]Stats'!B304 then in B43 use =INDIRECT("'"&B42) also note that if you close the source workbook indirect will not work -- Regards, Peo Sjoblom Excel 95 - Excel 2007 Northwest Excel Solutions www.nwexcelsolutions.com "It is a good thing to follow the first law of holes; if you are in one stop digging." Lord Healey wrote in message oups.com... Two situations: 1) Create two new workbooks 2) In one workbook (Workbook1) enter value of 200 in cell A1 3) In second workbook (Workbook2) enter [Workbook1]Sheet1!A1 into cell A1 4) In cell A2 in second workbook, enter =Indirect(A1) 5) I get back 200 Situation 2 1) I have two large workbooks 2) In one workbook, called [Award Stats], in worksheet 2006 Stats, in cell B304, I have value of 2,000,000 3) In another workbook, in cell B42, I have the following: '[Award Stats]2006 Stats'!B304 4) In cell B43 in second workbook, I have =Indirect(B42) 5) I get back #REF! Why the difference? Thanks - is there no way to do this w/o having the other worksheet open? The idea here is for a user to type in (or select from a validated list) the name of a file. The file will always be of a similar format (2006 Stats, 2005 Stats, etc.). In some cases it would be monthly rather than annually - not that that matters. I'd want to limit the user's work to just picking the file name w/o having to open all the other files as that sort of defeats the purpose. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Summarizing field quantities for specific records | Excel Worksheet Functions | |||
INDIRECT function do not work when other file is closed | Excel Discussion (Misc queries) | |||
Why does this Formula work? | Excel Worksheet Functions | |||
Using the Indirect function with a sheet number instead of a sheet name | Excel Worksheet Functions | |||
Some Excel links don't work | Excel Discussion (Misc queries) |