Home 
Search 
Today's Posts 
#1




Build formula using field values as text in the formula referencing another workbook
I want to build an Excel formula using field values as text in the formula referencing another workbook.
From an open workbook, I want to use a formula like this in cell A1: ='[FileName.xlsx]SheetName'!$x$# where SheetName is obtained from two different cells in the open workbook, and will change depending on the value of those two cells AND where $x$# is obtained from two cells in same open workbook and will change depending on the value of those two cells. In simple terms I want to combine two cells together to form the SheetName and Cell Address in the external file based on different variables in the current workbook. If cell B1 contains the value of 2012, and cell B2 contains the value of 12, then SheetName will equal B1 + B2 resulting in 201212 AND if cell C1 contains the value of 'M, and cell C2 contains the value of 15, then $x$# will equal C1 + C2 resulting in $M$$15 The final formula in cell A1 would in the above example be: ='[FileName.xlsx]201212'!$M$15 Obviously changing values in cells B1, B2, C1 and C2 would change the formula in cell A1, resulting in pulling different worksheet/cell values from external file. The issue is how to combine all these values into one cell that is a formula using some thing like CONCATENATE or INDIRECT maybe. I can get parts of it to work, but can't get a complete working formula. Thanks in advance for any help. Kirk 
#2




Quote:
Hi Kirk, Set up as your example above, the following formula in cell A1 should do the trick. =INDIRECT("'[filename.xlsx]"&B1&B2&"'!"&C1&C2) This would assume the two files are in the same directory. Hope that helps. S. 
#3




Quote:
I can't force users to open the other file, so I need a solution that will work when it is closed. Anyone know a way other than INDIRECT, or perhaps I am missing something in the way INDIRECT works? 
#4




Quote:
I think you'll have to go down the route of "custom functions". Have a quick search for "INDIRECT.EXT" for some further information. I've not tried it myself so cannot guarantee it will work for your particular conundrum.... 
#5




Quote:
Yes I try to give complete info, but didn't try closing the file until after I got it to work. I found the INDIRECT.EXT func and it works like a champ. Thanks for your help!! 
#6




Not a problem Kirk. Glad you got it fixed :)

#7




Quote:
Hey can I ask how you got this to work using the 'INDIRECT.EXE' function as I have very similar problem as you did above... 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
how to build a formula from a string of text  Excel Worksheet Functions  
Build excel formula using field values as text in the formula  Excel Worksheet Functions  
build external ref in formula from text?  Excel Discussion (Misc queries)  
Auto Update of a field referencing to a field in another workbook  Excel Worksheet Functions  
How do I formula a percentage with text values in the data field  Excel Worksheet Functions 