Excel Reference Question.

[Ninjahedge]

Hey peeps.

I have a problem I have been pulling my hair out about for a while.

I am looking for a way to take a reference from another file that is constructed from inputted cell references.

I found a way to do this with INDEX, but for some reason, INDEX only works when the other workbook is open. I tried some other functions, but they do not seem to have any way to get a constructed text address into the input field.

What I want to be able to do is specify the directory, the file name and the tab with a fixed cell reference to be able to grab numbers from different sheets on different directories.

ANY help would be GREATLY appreciated!

 

[Binky]

I'm not sure what you mean. Direct links to cells work fine in workbooks that aren't opened. By default, you are prompted to update the link upon opening the file. You can also force an update by going to Edit -> Links.

Are you trying to do a table lookup in a workbook that isn't open (i.e. are you using an index/match function?)?

EDIT: I think I see what you're trying to do. The REFERENCE is in a cell as text. I have used the Indirect function for this before, but it also need the worksheets to be open or you get a page full of REF errors.

I don't think you can do what you are trying to do. You can make a macro that will do what you need, but it would have to loop through the workbooks and open them to fill out the links/results.

 

[Ninjahedge]

Thanks Bink.

I was able to do a direct reference, but since the files were not named using typical characters, they needed the ' marks on either side of the name.

'N: \Project Blah\Documents\Calculations\[file name.xls]XL Tab'!$A$4 would be, I believe, the format. You type it in with an = in front and you get the cell value. But trying to find a way to construct that address and plunk it into a formula has proven difficult. (Note, there is no space between : and \. It was just to get rid of Mr Happy here :\)

The difficulty is, when using things like concatenate (&) it can create the full address field from independent cell references (Say, file name, directory, etc) but I thionk it has a problem because you cannot enter the ' character in the text field and have it work OR enter the ' character before a cell reference (it thinks "B20" is now a text field rather than a cell reference)

I could do it the long way, perhaps, with VB or a macro script that would construct the formula from pieces on the page, but that would require individual activation every time I changed something (and it would also be a PITA for someone not too familiar with VB!!!! )

Anyway, thanks for the help. If you hear of any way to do this, let me know!