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:

Please Login or Register  to view this content.

**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

**DonkeyOte** · 02-19-2009, 05:29 AM

Please Login or Register  to view this content.

The above would generate the following formula string:

Please Login or Register  to view this content.

Ampersand in VBA is used to concatenate strings, the use of quotations determines where you are entering text explicitly... perhaps we can explain using a simpler example, eg:

Please Login or Register  to view this content.

So in the first debug statement the second Ampersand in the code is seen by VBA as being part of an explicitly defined text string (& b) whereas is the 2nd & 3rd examples it is only being used to concatenate variables into one string. The 3rd example illustrates how we can insert explicit strings between our concatenated string variables.

So to generate your formula which if entered in native XL would be:

Please Login or Register  to view this content.

(whether I agree with the formula is different :-))

To generate the above in VBA you thus need:

Please Login or Register  to view this content.

So you use & to concatenate the string variables into one string... & should not be used literally (ie explicitly in the string itself) .. the only parts of the string that are fixed (ie no variables) are:

Please Login or Register  to view this content.

The remainder should be generated by concatenating the variables.

Make sense ?

(I guess re: the formula using in the way you're using it you could set both row & column index to 0 rather than using ROW(),COLUMN())

**Hein** · 02-19-2009, 06:10 AM

Hi DonkeyOte,
Thanks for the trouble of explaining the wonders of VBA and what you can do with it!!

Your explanation makes sense to me. It made me realise that all 3 declared variables are the same. For some reason I thought the DirectoryNameDeelEen and ...Twee were text. Maybe because their contents are text, while in real life they are also declared.

Next time I try to make a formula in VBA it must be easier!

I am always open for tutoring: what I am trying to do is to make a copy from another file/worksheet.
In your text you say:
"
(whether I agree with the formula is different :-))
"

How would you try to do this

Thanks again
Hein