How JustAnswer Works:

  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.

Ask NewITZone Your Own Question

NewITZone, Computer Enthusiast
Category: Microsoft Office
Satisfied Customers: 90
Experience:  I have been using Office Pro for 10 years and implemented applications for business use.
Type Your Microsoft Office Question Here...
NewITZone is online now
A new question is answered every 9 seconds

RA hs

Customer Question

I want to link a cell value from another excel file and use a value in a cell to tell what file i want to get the value from.

The normal way to link a value can look like this =+'c:\folder\[testfile.xls]sheet1'!$a$1

But i need a way to replace the file name and use a cell reference instead. For example:
=+'c:\folder\["testfile" & B1 & ".xls"]sheet1'!$a$1

This is for an Excel 2000 project.
Submitted: 3 years ago.
Category: Microsoft Office
Expert:  John D replied 3 years ago.

So you want the content of cell B1 to be added as a variable to end of the file name. Correct?

Can you give me an example of what content of cell B1 is?
Customer: replied 3 years ago.

Here is an example of data from B1: 081311


Expert:  John D replied 3 years ago.
Ok give me a few minutes to set it up...
Expert:  John D replied 3 years ago.
Ok the INDIRECT function needs to be used to build up the file name. However this function requires that the linked file MUST BE OPENED for it to work

Let me know if this condition is acceptable so I can send you the formula
Customer: replied 3 years ago.

Indirect will not work as the condition of having all files open will not work for this.



Expert:  John D replied 3 years ago.
Sorry in that case it cannot be done unless you resort to VBA code (i.e. using macro code or a third party add-in)