Creating an external reference with a variable

Hi all,

I'm wondering if there's any way to put a variable for a cell address in an external reference formula. Basically, I'm creating an abridged report of customer data that takes a single customer's info from 2 other workbooks on a networked drive and compiles the chosen cell data into one worksheet summary of their info. The base external reference formula looks like this:

='F:\Projects\[CustomerDB.xls]Sheet1'!VariableCellGoesHere

I want to know if there's a way I can make the formula use a variable cell address that I can just type into another cell to tell it which row I need to grab info from. Generally, all of the customer data I need is spanned across multiple columns in one single row. The formula above appears multiple times in the sheet I'm trying to make. The first is for "Name", the next is for "Address", next is "Phone Number", etc. So for instance, if I want customer data from the main spreadsheet and that customer's info is on row 355, my first few formulas are:

='F:\Projects\[ProjectDB.xls]Sheet1'!$A$355
='F:\Projects\[ProjectDB.xls]Sheet1'!$B$355
='F:\Projects\[ProjectDB.xls]Sheet1'!$C$355

Is there a way I can maybe have a designated cell where I just enter the row number I need (e.g. 481) so the formula sees that number and plugs it into the cell reference above resulting in the formulas adjusting themselves to ='F:\Projects\[ProjectDB.xls]Sheet1'!$A$481? Or is there another way to do this?

Any help/suggestion is greatly appreciated. Thanks.

Originally Posted by alansidman

I think that you can do this using the indirect function.

Look at this link

http://www.techonthenet.com/excel/formulas/indirect.php

In Cell A1 you would have your path as a string
In Cell A2 you would type 481
In cell A3 you would have this formula =Indirect(A1) & A2 to get your total formula

Hmmm, I'm trying this but it's not working. Maybe I'm not getting the syntax correct. I have:
A1 > F:\Projects\[CustomerDB.xls]Sheet1
A2 > 384 (decided to test this number instead of 481 since it's the one I'm working on)
A3 > =Indirect(A1) & A2

But I'm getting a #REF! error. Any suggestions?

Originally Posted by alansidman

You need to make sure that the apostrophe's and exclamantion point that you have in the actual formula are part of the string.

If necessary you may need to concatenate them to your string.

ie. in A1 " ' " & F:\Projects\[CustomerDB.xls]Sheet1 & " ' !"

You may have to play with this a bit to get it to work.


EDIT: My bad. You actually need to do this in cell A3

=INDIRECT("'"&A1&"'!"&A2)

Hmm, I entered exactly what you wrote above for A1 and A3. Excel is adding an extra single quote in the front of the string in A1 and I can't get it to stop doing that. I'm still getting the #REF! error. I've never used this formula before so I'm pretty confused by all the quotes and ampersands... Sorry to be so clueless. Your help is appreciated.

Originally Posted by alansidman

I truly don't know. Did you see my Edit about the file not being open?

BTW: Forum Courtesy is to only copy and quote that part of the preceding response that is relevant to your remarks. Otherwise it takes up real estate and does not add to the conversation.

The problem was that I had 2 separate instances of Excel open so I could have one open on each of my screens. Apparently the reference won't work in this situation. I had to close both spreadsheets and reopen them both within the same instance of Excel for them to work.