index...indirect

**jw01** · 03-30-2011, 01:14 AM

hello

having some major issues with my use of indirect or index.

im trying to use the index formula.

here is the original formula that im trying to "modify" as i have 180 rows to apply it to with different references.

original formula:
=INDEX('J:\Sales Analyst\SWAT\Archieve 2011\P1W1\[Edmonton-Winnipeg.xls]Peter Vepstas'!$A$1:$X$63,I4,I5,1)

modifying it to:
=INDEX("'J:\Sales Analyst\SWAT\Archieve 2011\"&I8&"\["&D9&".xls]"&E9&"'!"&E4&"",I4,I5,1)

where
I8 = P3W1
D9 = Edmonton-Winnipeg (branch name)
E9 = name
E4 = A1:X63

...is there something in doing wrong?...im getting a #REF error. can someone pls help, ive been stuck on this for over a day. thx you.

i have attached a sample, highlighted in red is the original; yellow shows what im trying to achieve. thxs!

**DonkeyOte** · 03-30-2011, 02:42 AM

Your paths imply target file is closed - the native INDIRECT function does not work with closed targets.

If you need this formula functionality Google INDIRECT.EXT part of morefunc add-in
Note: morefunc.xll can not be "embedded" for distribution from XL2007 onwards meaning it would need to be installed on each client using the file

If you can't install 3rd party software you will need to use VBA: you can use VBA to re-write the formula links else use Harlan Grove's PULL Function [Google for more info.] and/or equivalents thereof.

**jw01** · 03-30-2011, 08:49 AM

Hello

thxs for your input.

even when i have the "source" file open, im still getting a value error.

it's wierd bc when i have the index formula w/o referncing to cells on the worksheet, i see the value of i.e. $400 (even with the workbook closed).

however, when i place in factors $E$9 etc to look off the sheet, the i get a #value error. i thought the way i had the "" places or my logic was not right.....ur thoughs on that?

**Cutter** · 03-30-2011, 09:36 AM

You are not using your concatenation within the INDIRECT() function.

**jw01** · 03-30-2011, 10:22 AM

hello

i tried to use the "indirect.ext" function..however need some help.

this is my formula
=INDIRECT.EXT(D5,TRUE)

where
d5 = 'J:\Sales Analyst\SWAT\Archieve 2011\P1W1\[Edmonton-Winnipeg.xls]Peter Vepstas'!$A$1:$X$63

it is however, referencing to cell A1 in that workbook. how do i make it reference to a particular row/column? thxs

also, cutter, im not sure what you mean by "concatenation"...bc i thought i had the &"" and all that added to my indirect? thxs

**Cutter** · 03-30-2011, 11:41 AM

I'm referring to the formula you have in I12 of your supplied sample.

There is no INDIRECT() function within the INDEX() function. That's what I mean.

As for your INDIRECT.EXT() problem:

I read here that INDIRECT.EXT() references single cells on a closed file - not ranges.

http://webcache.googleusercontent.co...sed-workbooks/

**Cutter** · 03-30-2011, 11:49 AM

This link:

http://xcell05.free.fr/morefunc/engl...direct.ext.htm

indicates you can retrieve values from a range but if you look at the last example (which is the only one involving a range) it is entered as an array formula (Ctrl+Shift+Enter)

**jw01** · 03-30-2011, 02:44 PM

hey cutter

thxs for your response and help.

now im thinking this indirect ext seems somewhat useless to my need.

basically, i would like for the formual to be versatile, so when i drag it down, it can automatically adjust to the "city" and "employee name".

the path remains the same i.e.
J:Sales analyst\SWAT etc etc

but the portion that would change is...P3W1, City name, employee name.

that is why i wanted to use my index or even indirect, but...can you help me with the indirect, without the ext?

this is what i have but it seems to not be working correctly:
=INDIRECT(E3&I8&"\["&D9&".xls]"&E9&"'!"&I4&I5)

**jw01** · 03-30-2011, 04:46 PM

hello

i have tried to incorporate the index(index) together....but even with the workbook open, im still getting a REF error for some odd reason.

=INDEX(INDIRECT(E3&I8&"\["&D9&".xls]"&E9&"'!"&E4),I4,I5,1)

is my "" or & not being used correctly?

i have even tried it using the following...
=INDEX(INDIRECT("'J:\Sales Analyst\SWAT\Archieve 2011\"&I8&"\["&D9&".xls]"&E9&"'!"&E4&""),I4,I5,1)

**jw01** · 03-30-2011, 05:16 PM

hey guys

thxs for the help. i finally managed to cracked to code:

=INDEX(INDIRECT.EXT("'J:\Sales Analyst\SWAT\Archieve 2011\"&I$8&"\["&$D9&".xls]"&$E9&"'!$A$1:$X$63"),I$4,I$5,1)

thxs for your help and advice!