Array parameters in different file

[Benoot]

Hello,

I am having difficulty setting array parameters.

I want to use a local cell from the spreadsheet to define the array in a different file I am pointing to.

ie: I want "C32" and "D46" to be defined in my local spreadsheet.


=(VLOOKUP(H$1,'C:\Users\ian.benoit\Desktop\DESKTOP\[QUICKBOOKS DATA.XLSX]Sheet1'!$C32:$D46, 2, FALSE))*1440

Thanks in advance

Ian

[daffodil11]

You will need to use the INDIRECT function.

INDIRECT evaluates its contents as a reference, instead of the string or other variables contained in it.

So, if in C32 of your active worksheet you put A1 and then in D46 you out B10, you could used SUM(INDIRECT(C32&":"&D46)) to really evaluate SUM(A1:B10).

Note how you need to still represent the colon, which is concatenated between the references with & and in quotes.

In your example, you might use

=VLOOKUP(H$1,'C:\Users\ian.benoit\Desktop\DESKTOP\[QUICKBOOKS DATA.XLSX]Sheet1'!&INDIRECT("$"&C32&":$"&D46), 2, FALSE)*1440

[Benoot]

I am unsure if my syntax is correct, it does not appear to be accepting my algorithm.

I used this :

=(VLOOKUP(H$1,'C:\Users\ian.benoit\Desktop\DESKTOP\[QUICKBOOKS DATA.XLSX]Sheet1'!INDIRECT("$C"&C4&":$D"&D4, 2, FALSE))*1440

As you can see, the columns (C and D) are always the same I am just change the row numbers.

[martindwilson]

indirect needs to be the whole path, workbook like this
=VLOOKUP(H1,INDIRECT("'C:\Users\acer\Documents\forum examples\[Book3.xlsx]Sheet1'!$C$"&C4&":$D$"&D4),2,FALSE)

[Benoot]

Thanks again for all your help!

I still get a #ref error in this algorithm:

=(VLOOKUP(H$1,INDIRECT("'C:\Users\ian.benoit\Desktop\DESKTOP\[QUICKBOOKS DATA.XLSX]Sheet1'!$C"&C4&":$D"&D4), 2, FALSE))*1440

the value of C4:32
D4:46

[martindwilson]

is the workbook open? indirect will not work on a closed workbook,

[Benoot]

Yes quickbooks data.xlsx is open

[Benoot]

I can post the files if you like!

[martindwilson]

well that works fine for me that is my actual path i also tried using INDIRECT.EXT() from the more func add in that works on closed workbooks
http://www.thefreewindows.com/369/67...ons-for-excel/ (DONT KNOW IF IT WORKS IN 2010 THO)

=VLOOKUP(H1,INDIRECT.EXT("'C:\Users\acer\Documents\forum examples\[Book3.xlsx]Sheet1'!$C$"&C4&":$D$"&D4),2,FALSE) and it works fine

[Benoot]

The admin isn't in right now to give permissions to download those functions.

I have attached the files I am using.

[martindwilson]

=VLOOKUP(H1,INDIRECT("'C:\Users\acer\Downloads\[QUICKBOOKS DATA.XLSX]Sheet1'!$C32:$D46"),2,FALSE) works ok but if yo reference a cell

=VLOOKUP(H1,INDIRECT(E18),2,FALSE) the leading ' is not seen so in effect it is looking at
C:\Users\acer\Downloads\[QUICKBOOKS DATA.XLSX]Sheet1'!$C32:$D46
and not
'C:\Users\acer\Downloads\[QUICKBOOKS DATA.XLSX]Sheet1'!$C32:$D46 to fix that you have to add another one so the
entry is in the cell as
''C:\Users\acer\Downloads\[QUICKBOOKS DATA.XLSX]Sheet1'!$C32:$D46

[Benoot]

Your last post confused me a bit Martin,

If you are looking at "quickbookstest1" The line I am attempting to work on is highlighted and reads:

=VLOOKUP(H$1,INDIRECT("'C:\Users\ian.benoit\Desktop\DESKTOP\[QUICKBOOKS DATA.XLSX]Sheet1'!$C"&C4&":$D"&D4), 2, FALSE)*1440

[martindwilson]

=VLOOKUP(H1,INDIRECT("'C:\Users\acer\Downloads\[QUICKBOOKS DATA.XLSX]Sheet1'!$C"&C4&":$D"&D4),2,FALSE)
works ok for me in quickbookstest1

[Benoot]

Strange, I still get a #REF! error. It does not ask me to select the quickbooks data file after I input the function.

I will try some more to figure it out.

[Benoot]

I cannot seem to solve the problem, I keep getting #REF!. Could this perhaps be because my excel version is 2010?

[Benoot]

I believe the problem is that once I start using INDIRECT() I don't select the other excel file I am pointing to once I close the function?

[martindwilson]

Create a folder C:\TEST
its then easier to test different things
i can duplicate that file path and see if it works in fact this is working if i store
QUICKBOOKS DATA.xlsx in c:/test ‎
=VLOOKUP(H1,INDIRECT("'C:\test\[QUICKBOOKS DATA.xlsx]Sheet1'!$C"&C4&":$d"&D4),2,FALSE)
see attached

[Benoot]

thankyou. I ended up not using the whole path directory and it seemed to work. I only used [QUICKBOOKS DATA.XLSX]Sheet1'!

instead of the whole C:\ path

Thanks for your help!