Vlookup changing cell

[Vadimchik]

Hi everyone,

I'd like to use vlookup with a table array that changes according to the month(the files are sorted according to the month).

The changing month is no problem of course, what I'd like is to use vlookup with a table array that is based partly on a cell, for example:

D8: Vlookup(D2,'G:\Collection Files\[$E$4.xls]Sheet1'!$A$3:$Q$220,7,0)
D9: Vlookup(D2,'G:\Collection Files\[$E$4.xls]Sheet1'!$A$3:$Q$220,11,0)
D10: Vlookup(D2,'G:\Collection Files\[$E$4.xls]Sheet1'!$A$3:$Q$220,13,0)
D11: Vlookup(D2,'G:\Collection Files\[$E$4.xls]Sheet1'!$A$3:$Q$220,16,0)



I have provided an example - results should be in cells D8:D11,

Not sure where to start, would love any help on this matter.


Thank you

[etaf]

I'd like to use vlookup with a table array that changes according to the month(the files are sorted according to the month).

how do you want to change the month name of the file

you could use indirect , ( will not work on closed files - you need the indirect.ext to work on closed files )

so in a cell you could put the month name part of the file name

lets assume thats in cell A1 and as an example - just the word april (unless you want years and then the file needs to include years )

= vlookup(B7, INDIRECT.EXT ( "G:\Collection Files\"&A1&" - (E4).xls",7,0)

=VLOOKUP(A1, INDIRECT.EXT("'"&D3&"Excel\indirect\Indirect from workbook\[settings.xlsx]sheet1'!A1:B2"),2,0)

Can you explain a little more on the
"vlookup(B7,G:\Collection Files\"Month name" - (E4).xls,7,0)

are you taking the value in E4 away ?

[Vadimchik]

Hi,

I installed the indirect.ext not sure about how to apply your line of code here:
=VLOOKUP(A1, INDIRECT.EXT("'"&D3&"Excel\indirect\Indirect from workbook\[settings.xlsx]sheet1'!A1:B2"),2,0)

The files are arranged in this way: G:\Collection Files\2014\January.xls,February.xls,March.xls and so on
in the same way I also have G:\Collection Files\2015\January,xls,February.xls,March.xls.

What I would like is to combine the search using the year aswell so it would apply to years aswell.

Regarding "vlookup(B7,G:\Collection Files\"Month name" - (E4).xls,7,0)
It should be like this: vlookup(B7,'G:\Collection Files\2014\[January.xls]sheet1'!A$3:$E$220) where in column A are listed all the employee numbers and C would be the one im trying to pull.

Thanks for the help (:

[etaf]

sorry about that - my example was for files I have as tests for this sort of application

So if you put the month and year into two cells
lets say A1 and B1
A1 = January
B1 = 2014

then you use the indirect as follows
indirect.ext ( "'G:\Collection Files\"&B1&"\["&A1&".xls]sheet1'!A$3:$E$220"

so in vlookup

=vlookup ( B7, indirect.ext ( "'G:\Collection Files\"&B1&"\["&A1&".xls]sheet1'!A$3:$E$220", 7, 0 )
should do it
BUT your range is A to E - so thats 5 columns , and so you cannot return column 7 without extending the range to be A to G

[Vadimchik]

Great Idea,

Not working though returns an #REF statement.

Although the Paths are correct.

[etaf]

can you attach two spreadsheets
the master you are using vlookup in and one of the months files
i can create the folders on my C drive and see if i get it to work
and then all thats needed is the G changed to C

[Vadimchik]

Hi,
Attached are the two spreadsheets.

The columns to be drawn in the January File : D, F , H , J

To be put into F16:F19 in the main file

Thanks!

[etaf]

so this is working for me
=VLOOKUP(B7,INDIRECT.EXT("'C:\Collection Files\"&E5&"\["&E4&".xlsx]Sheet1'!A$1:$H$14"),1,0)
i chnage the sheet name to sheet1 as well in January file

I had to change the range though - for some reason ,if i go to H15 - I then get the #REF errors

E5 = 2014
E4 = january

no idea why the range cannot extend ??

so i selected the cells and cleared contents A6 to M300 on january.xlsx


and now it works

[Vadimchik]

That did it, awesome job !

Thanks a lot!

[etaf]

your welcome
if my assistance has helped, and only if you wish to , there is a reputation icon * on the left hand side - you can add to my reputation here
If you have a solution to your thread - Please mark your thread solved do the following: >
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

[Vadimchik]

Unfortunately, Only works when the other workbook is open for some reason.
When I close it it shows #REF, such a shame.

Don't know why, should work on closed workbooks aswell.

Any thoughts?

[etaf]

yes, it should do - I think its something to do with the way your work book is setup - as i had to clear cells to get it to work before

indirect.ext - does work on closed workbooks

I can try and have a look , but it took quite a while to find the need to clear cells - it maybe quicker to setup the sheets a fresh

[Vadimchik]

Okay, Interesting. Which cells did your clear?

I'll try to create the file from scrath.

Thanks.

[etaf]

see post #8

so i selected the cells and cleared contents A6 to M300 on january.xlsx

[Vadimchik]

Okay so I did a sensitivity report.
Turns out the problem is because of the max cell refrence - when I limit it to 200 for the first cell and 100 for the second cell - it works.

Thanks!

[etaf]

strange, wonder what is going on there, i had similar issues , if i used range over row 15

[Vadimchik]

Yeah strange,
The problem is that the file takes a long time to calculate each time you input something.
It's like the excel is searching the file every single time.

[etaf]

yes, it will do all the calculations as its a "volatile" i think the word is

if you goto advanced you could turn off calculation and set to manual
I think F9 does that too
or F9 calculates and you could use that after all the data entered

[Vadimchik]

There is a way to solve this actually.
=VLOOKUP(B7,INDIRECT.EXT("'C:\Collection Files\"&E5&"\["&E4&".xlsx]Sheet1'!A$1:$H$14",FALSE),1,0)

IF you insert false after your array it will stop the manual calculations

Hope this helps someone, as a lot of people seem to have troubles with this.

[etaf]

Thats intesting thanks for that

I had ,0) for false in the vlookup()
i was not aware of the false working in indirect()
##If a1 is FALSE, ref_text is interpreted as an R1C1-style reference.

thanks for that