Link cells to a different workbook file question

I am wanting to compile a bunch of data from other workbooks that are all in similar format.

I want to setup a single cell that tells all the other cells which file to reference for each group of analysis I have.

I know how to link specific cells to other workbook files. Although in order to make this run smoother and a lot less manual entering I want to have the ability to have a cell that tells where the file is that the rest of the cells reference.

So if A1 is the cell that references the workbook file that is not part of the current workbook then cells B1, C1, etc will know that it needs ='[Filename]Sheet Name' and the designated cell to pull from the other workbook.

So if B1 is supposed to display cell H113 from the other workbook it should reference A1 and insert ='[Filename]Sheet Name' and then attach H113 to the ='[Filename]Sheet Name'H113

Here is an example with filenames and sheet names included

A1: '\\SERVER\Property\[ABC Inc - 123 Main St - Property Analysis.xlsx]2012 Season' - This is where the file is located.

B1: =IF(A1<>"",A1 with H113 cell attached to end of string, "")
C1: =IF(A1<>"",A1 with H114 cell attached to end of string, "")

Note: A1 would be entered manually and if possible have the rest of the sheet fill itself out.

Is this possible to do?


Hopefully that is explained correctly, please let me know if I need to explain it differently.

Thanks for any help!

Hi,

You can concatenate the strings easy enough but getting the data from the other workbook if it is closed needs to be addressed. Excel's native INDIRECT function will not return the data if the other workbook is closed. There is an Excel add-in called Morefunc by Laurent Longres which has an INDIRECT function that will do it though. You can download that here http://download.cnet.com/Morefunc/30...-10423159.html. To concatenate your formula would look something like

=IF(A1<>"",A1&"H113", "")

HTH

Steve

I thought you wanted to return the values from a specific cell no? If so you have to provide the cell reference somehow. Simply putting in the path to the workbook sheet won't return anything as I see you've found so in answer to your question not unless you're trying to create a hyperlink. You could have the path in one cell and the cell reference in the cell next to it (determined by some logic to populate) and then in C1, D1 use the INDIRECT.EXT formula with a concatenate to A1 & B1 to come up with the path/cell reference.

Steve

My goal is to have one cell be the main reference for the rest of the cells to use. So when referencing one workbook the rest of the cells would display the desired cells from that workbook.

The form I'm referencing uses the same ranges for the cells but I need to have the ability to change what workbook it pulls from.

Is that anything that is possible?

You could create a drop down list of the paths and select whichever one you wanted the cells to use. So create the list of paths, use Data Validation to refer to the list in A1. Each time you change the path selection, the cells would update to reference that workbook.

How do you create a drop down list of workbooks to reference if you want to use a specific folder that all files are located in?

Are you trying to combine all of the workbooks in one calculation or do you just want to be able to see each one individually?

This utility may be what you're looking for.


http://msdn.microsoft.com/en-us/libr...ffice.12).aspx

Originally Posted by SteveG

This utility may be what you're looking for.


http://msdn.microsoft.com/en-us/libr...ffice.12).aspx

I haven't dealt much with VBA so I'll have to look at that.

I've attached an image of what form I'm using. I have this form replicated on one sheet and want to use it for each property. If the top left is A1 then A1 would be chosen from a drop down list of the properties located within a specific folder. Then the rest of the cells will pull the information from that workbook. Date 1 would pull the cell that displays that date from the workbook.

Does that make more sense?

Knowing what your criterias are would be helpful. Can you post an example of the end result and how you got there (which workbook was chosen to pull from and why?)?

Steve

I think this is what you're after. Just replace the path name with your own and the file names with your own to test it out.

HTH
Steve

Here is another wrinkle, within the workbooks are multiple different sheets, the sheets that I want to reference in each workbook is named '2012 Season' so what changes do I need to accommodate that?

Thanks for all your help so far by the way, I am a doing this without much formal excel training.

Edit: Never mind, I see you have that accounted for on your example sheet.

I've put in my path and workbooks but it only displays =INDIRECT.EXT($A2$&$B$2&"cell #") and not any values. Is it supposed to output the values from the sheet?

Just need to change the display settings. Select the Office Button on the upper left hand corner. At the bottom right select Excel Options, then Advanced. Scroll down until you see "Display options for this worksheet" and deselect the "Show formulas in cells instead of their calculated results". I left it selected so you could see the formulas.

Signing off now.

Steve

That worked great, time to implement it! Thanks a bunch SteveG.

Glad you got it working.

Cheers,
Steve