Solved

Excel Cells with external sheets

Posted on 2014-10-02
2
156 Views
Last Modified: 2014-10-16
Hello Experts Exchange
I have a spreadsheet that has formulas that point to different spreadsheets, for example a cell has the following formula.

='\\ntserver2\Losses\09\AC4\UKL\[UKLBlue593.xls]Lost Time'!C32

Is it possible that I have this formula built by have the file location in a cell?

So for example.
Cell A1 has this in it;
'\\ntserver2\Losses\09\AC4\UKL\[UKLBlue593.xls]Lost Time'!

Then cell A2 uses cell A1 to make it formula, so it will have;
=A1 & C32

Then it will get the information from cell c32 from the spreadsheet detailed in A1?

Regards

SQLSearcher
0
Comment
Question by:SQLSearcher
2 Comments
 
LVL 24

Accepted Solution

by:
Phillip Burton earned 250 total points
ID: 40356495
Almost - you can use INDIRECT, but the C32 will need to be in quotation marks:

=INDIRECT(A1 & "C32")

Open in new window

0
 
LVL 32

Assisted Solution

by:Rob Henson
Rob Henson earned 250 total points
ID: 40356630
I was also thinking INDIRECT but unfortunately INDIRECT does not work when the source file is closed.

There is an addition to INDIRECT available from a MOREFUNC add-in.

This gives INDIRECT.EXT which does work when the source file is closed.

Thanks
Rob H
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question