Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Junior Member
 
Posts: 3
Default 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   Report Post  
Senior Member
 
Posts: 663
Default

Quote:
Originally Posted by solardirect View Post
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

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   Report Post  
Junior Member
 
Posts: 3
Default

Quote:
Originally Posted by Spencer101 View Post
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.
It appears that the INDIRECT function only works when both files are open. I have tried to reference both filename.xlsx and the full address 'C:\dir\filename.xlsx. Both work as long as the file is open; as soon as you close the referenced file, and recalculate the worksheet with the formula, it returns a #REF! error.

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   Report Post  
Senior Member
 
Posts: 663
Default

Quote:
Originally Posted by solardirect View Post
It appears that the INDIRECT function only works when both files are open. I have tried to reference both filename.xlsx and the full address 'C:\dir\filename.xlsx. Both work as long as the file is open; as soon as you close the referenced file, and recalculate the worksheet with the formula, it returns a #REF! error.

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?
Your original post didn't specify that the second file would be closed. Just that sort of information that makes the difference between getting a right answer and a wrong one!

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   Report Post  
Junior Member
 
Posts: 3
Default

Quote:
Originally Posted by Spencer101 View Post
Your original post didn't specify that the second file would be closed. Just that sort of information that makes the difference between getting a right answer and a wrong one!

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....
Spencer,

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   Report Post  
Senior Member
 
Posts: 663
Default

Quote:
Originally Posted by solardirect View Post
Spencer,

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!!
Not a problem Kirk. Glad you got it fixed :)
  #7   Report Post  
Junior Member
 
Posts: 1
Default

Quote:
Originally Posted by solardirect View Post
Spencer,

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!!


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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
how to build a formula from a string of text DR Excel Worksheet Functions 2 March 7th 08 01:12 AM
Build excel formula using field values as text in the formula val kilbane Excel Worksheet Functions 2 April 18th 07 01:52 PM
build external ref in formula from text? klp2344 Excel Discussion (Misc queries) 1 January 6th 07 07:06 PM
Auto Update of a field referencing to a field in another workbook Camper Joe Excel Worksheet Functions 1 February 14th 06 07:14 PM
How do I formula a percentage with text values in the data field nancy wilson Excel Worksheet Functions 0 September 18th 05 09:21 PM


All times are GMT +1. The time now is 02:40 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"