hi friends,
I have several files from which I have to look up specific value and append in single file.
For this I am facing two problems
1. Many Source Files needs to be opened simultaneously
2. Dynamic path input is required
Kindly guide.
hi friends,
I have several files from which I have to look up specific value and append in single file.
For this I am facing two problems
1. Many Source Files needs to be opened simultaneously
2. Dynamic path input is required
Kindly guide.
Hi and welcome to the forum
why do you say they need to be opened? vlookup and index/match (in fact most functions) will work on closed workbooks.
I suggest you give a bunch more detail on what you have, what you want and how you get that
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Hi FDibbins,
Thank you for warm welcome and quick reply. Detailed condition would be as below.
a) I have sets of <strings> with specific number pattern in FileA.xlsx
i.e.
<string> =
PA52.0_CA42.0_TA56.0
PA52.0_CA42.0_TA57.0
PA52.0_CA42.0_TA58.0
PA52.0_CA42.0_TA59.0
PA53.0_CA42.0_TA56.0
PA54.0_CA42.0_TA57.0
PA55.0_CA42.0_TA58.0
PA52.0_CA42.0_TA59.0
total 7Lacs such strings in FileA.xlsx coulmn A
b) Those <strings> has specific value generated in another file in 9th column which to be looked up. say those file name are with first few latter of strings
i.e.
File_PA52.0.xlsx
File_PA53.0.xlsx
File_PA54.0.xlsx etc
I want to write vlookup function in FileA.xlsx such that
=vlookup($A1,concatenate("'C:\Folder\File_",left($A1,6),".xlsx'!$A:$I",9,FALSE)
But it returns nothing....
Waiting for your reply, and excuse and let me know if there are still confusion in above detail.
When you concatenate (join) different pieces of text (and/or numbers), all you end up with is...more text - which excel cannot use as a reference for anything. What you need, is to use INDIRECT() to convert that text into a reference that excel can then use in formulas.
However, INDIRECT() does not work on closed workbooks, so for this, you need to install the MOREFUNC add-in - see this link (there are many many more if you google morefunc)
http://www.ashishmathur.com/tag/morefunc/
Friend,
Seems like it will work after soling suggested addin. But I want your further help as in our office external software is not allowed to install
Can I get it's vba query directly?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks