Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
substitute the filename in a cell reference with a string in another cell.
Hi all,
suppose I have this cell reference: ='c:\[file.xls]sheet1'!X1 and I have the file name "file.xls" in cell A1 on my active worksheet Is there a way to any of the following: - include the string in A1 in the above cell reference between the square brackets? - take the complete path (c:\[file.xls]) from a cell on my worksheet? or do I have to TYPE the complete path in the above cell reference? Thanks for thinking about it. Hans |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
substitute the filename in a cell reference with a string in anoth
Hi,
Assuming "file.xls" is in A1, type the following in any cell of active sheet. ="'c:\["&A1&"]sheet1'!X1" and take the result of this cell. With kind regards, NAVEEN "flummi" wrote: Hi all, suppose I have this cell reference: ='c:\[file.xls]sheet1'!X1 and I have the file name "file.xls" in cell A1 on my active worksheet Is there a way to any of the following: - include the string in A1 in the above cell reference between the square brackets? - take the complete path (c:\[file.xls]) from a cell on my worksheet? or do I have to TYPE the complete path in the above cell reference? Thanks for thinking about it. Hans |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
substitute the filename in a cell reference with a string in another cell.
Hans
To include another cell's value in the reference you need to use the INDIRECT function =INDIRECT("'c:\["&A1&"]sheet1'!X1") To get the full path you use the CELL function. CELL("filename") will return the full path, followed by sheet name. To get just the path: =LEFT(CELL("filename"),FIND("]",CELL("filename"))) HTH Kostis Vezerides |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
substitute the filename in a cell reference with a string in anoth
Hi Naveen,
thanks for your reply. That's how far I had got. But the next bit is the interesting part. When you say "take the result of this cell" how would you do that? Let me give you an example. Suppose X1 actually is a range X1:X10 How would you incorporate the string you suggested (lets say in B1) into e.g. an =average function? =average(????,X1:X10) Thanks, Hans |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
substitute the filename in a cell reference with a string in anoth
I had not realized that INDIRECT is not needed for the workbook part of
a reference (thanks for the post Naveen). But it certainly is needed for range specifications in functions =AVERAGE(INDIRECT("'c:\["&A1&"]sheet1'!X1:X10") HTH Kostis Vezerides |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
substitute the filename in a cell reference with a string in anoth
Thanks all for the excellent brainwork!
Till next time. :-) Hans |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
substitute the filename in a cell reference with a string in anoth
Sorry, it doesn't work. Always delivers a #REF.
A1: Example.xls =INDIRECT("'C:\["&A1&"]Expenses'!$G$12") gives #REF This is what ="'C:\["&A1&"]Expenses'!$G$12" delivers: 'C:\[Example.xls]Expenses'!$G$12 Maybe it's not possible at all? Hans |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
substitute the filename in a cell reference with a string in another cell.
Hans, I tested all the solutions I provided. If your formula delivers 'C:\[Example.xls]Expenses'!$G$12 and you want the INDIRECT of this and you are getting #REF!, is it possible that: - Example.xls is not directly in C:\ ? - Example.xls does not contain a sheet Expenses? Regards Kostis flummi Wrote: Sorry, it doesn't work. Always delivers a #REF. A1: Example.xls =INDIRECT("'C:\["&A1&"]Expenses'!$G$12") gives #REF This is what ="'C:\["&A1&"]Expenses'!$G$12" delivers: 'C:\[Example.xls]Expenses'!$G$12 Maybe it's not possible at all? Hans -- vezerid ------------------------------------------------------------------------ vezerid's Profile: http://www.excelforum.com/member.php...o&userid=28481 View this thread: http://www.excelforum.com/showthread...hreadid=514836 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
substitute the filename in a cell reference with a string in another cell.
Thanks Kostis,
for investigating this. Hope I don't take too much of your time. What I did is this: I loaded the workbook "example.xls Then I created a new workbook In this new workbook I created a simple cell reference to a cell in example.xls Then I closed example.xls That gave me the full path in my reference in the new workbook. Then I copied the filename "example.xls" from the above reference into cell A1 in my new workbook Then I copied the formula into another cell and modified it as you suggested (indirect(....) That gave me the #REF whilst the old formula still delivered the correct result. Here's a "screenshot" Example.xls 904 #REF! ='C:\[Example.xls]Expenses'!$G$12 <-- formula that delivers 904 =INDIRECT("'c:\["&A1&"]Expenses'!$G$12") <-- formula that delivers #REF 'c:\[Example.xls]Expenses'!$G$12 <-- this is what I get when I strip off the indirect() function from the previous command Hans |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
substitute the filename in a cell reference with a string in another cell.
Hans,
I think it has to do with the file being closed. If you insert INDIRECT while the referenced file is open it will display correctly 904. If you close it nothing will change until recalculation takes place. Try it and let me know. Kostis |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
substitute the filename in a cell reference with a string in another cell.
Correct!
When the file is open it displays the correct result. Thanks Kostis, for taking the time. Hans |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
substitute the filename in a cell reference with a string in another cell.
You are welcome Hans. Glad to be of help.
Kostis |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do I convert text string into a cell reference | Excel Discussion (Misc queries) | |||
Maintaining cell reference after sorting | Excel Discussion (Misc queries) | |||
indirect function to reference cell on different sheet | Excel Worksheet Functions | |||
dynamic cell reference within a text string | Excel Worksheet Functions | |||
Cell Reference Math | Excel Worksheet Functions |