INDEX as an alternative for INDIRECT

**Hein** · 02-18-2009, 04:06 AM

Hi all

In 1 of my spreadsheet I make use of the function INDIRECT to access cells in another spreadsheet. This works fine but has the disadvantage that both file have to be open.

It seems that INDEX can do the same, but: the sourcefile doesn't have to be open. I have tried it and this works.

However: the directory of the file I'm working on will change in the near future (maybe more than once)
Therefore I want 1 central place with the directory and file names and use these in the INDEX function

This is where I get into trouble
I'm not sure if it is possible, but if it is, some advice is needed on how to do this.

I have added a demo to demonstrate what I want to do
I am not sure why it doesn't work.
Is it the '? I have tried multiple solutions, but not luck

Thanks in advance
Hein

PS
Maybe I am on to something
If I open the function builder the 1st line is: "C:\....
Somehow there appears a " in front of the function causing it to malfunction??
How to get rid of this?
Removing the ' in cell C20 has no effect

**DonkeyOte** · 02-18-2009, 04:11 AM

Wherever you're trying to create a "dynamic range" (ie variable file etc...) you're obliged to use INDIRECT I'm afraid.... you can look at using INDIRECT.EXT which comes bundled in the morefunc.xll and works with closed targets... other than that you're other alternatives are VBA orientated... you could for ex. think about using VBA to re-write formulae at press of a button to re-point to a different file ... not necessarily trivial but sometimes worthwhile... IMO the best approach is store the data held in these numerous files into one central repository be it another file or a database... ie fixed target.

Others may disagree but the above IMO (FWIW).

(Another alternative depending on volume of values being retrieved...http://spreadsheetpage.com/index.php...a_closed_file/)

**Hein** · 02-18-2009, 04:24 AM

Thank You DonkeyOte for your reply

Rebuilding the function using VBA is a very usable option, thanks for the suggestion

Using the INDIRECT.EXT means, I believe, installing 3rd party software. This is not an option since we are not allowed to install this.

Did you see my Edit - remarks (the PS), I think our messages crossed each other.

Anyway, I will use my VBA skills again

Thanks
Hein

**Hein** · 02-18-2009, 08:21 AM

I have the code working, apart from 1 little thing that I can't figure out

During the code I determine the name of the current worksheet
(The target worksheet in the source file has the same name).

When I keep the mousepointer over the sheetname a popup appears with the sheetname "Target".

However: when the code is run it asks to confirm which sheet is needed in the sourcefile. The name it gives is "_Target"

Is this "_" the problem and how can I get rid of it?

I have added a demo to demonstrate what happens

How can I fix this?

Thanks in advance
Hein

**DonkeyOte** · 02-18-2009, 11:33 AM

I'm not sure I follow quite what you're looking to do but in principle (if nothing more) you need:

ActiveCell.Formula = _
    "=INDEX(" & DirectoryNameDeelEen & Sheetname & DirectoryNameDeelTwee & " , Row(), Column())"

**Hein** · 02-19-2009, 03:43 AM

Hi DonkeyOte,

Thanks, it had be to be something with the " and the &.
This was 1 of the many versions I have tried, but I must have made an error on the way.

What I'm trying to do is to create a path to the directory, file and worksheet I need to get the data from.

The 1st part ("'C:\Temp\[SourceFile.xls]") is the directory and nam of the file.

The middle part is the name of the worksheet (worksheetname = Target). I use the same name for the corresponding worksheets in the source and in the workfile. Therefore by determining the worksheet name in the workfile, I have also the name of the wanted worksheet in the sourcefile.

The part ("'!$1:$65536") is the area where index searches for the data.

The last part (, Row(), Column())") from the formula is the target cell.

The outcome is a duplicate from the source file/worksheet

This is not the first time I am having trouble with the & and the "
Is it possible to shed light on the theory behind it?

Thanks anyway
Hein