Tag an excel file through text

[sudipto3003]

in a folder i have more than 100files in xlsx format, now in a new excel sheet i have the data in cell "A2" to "A51", and the 100file names are in cell "B1" to "B101". without opening those files separately is it possible to find data from each file from Sheet1 using VLOOKUP.

 

[Luke M]

You could write a short macro to write all the formulas for you, using data from the cells in B1:B101. It would be best to record a macro of you doing one manually, and then just modify the code to use the correct variable.

Psuedo structure

[pre]

Sub MakeFormula()
For i = 2 to 51
cells(i,"C").formula = "=VLOOKUP(A" & i & ","[C:/My Documents/" & cells(i,"B").value & ".xlsx]Sheet1!A:C,3,FALSE)"
next i
End Sub

[/pre]

 

[sudipto3003]

thanks luke,

[pre]

A	      B	            C	           D	    E	            F
1	  DATA	    ADYAR	AHMEDABAD	AKOLA	ALLAHABAD	AMARAVATI
2	29410729410
3	29411029450
4	47604547600
5	47604747630
6	47614947672
7	47628047642

[/pre]
sorry for the previous post, actually my data table looks like above, now in row 1 there are the file names like ADYAR,AHMEDABAD, etc. in xlsx format, now all those files are located in D:DocumentsClosing folder, now i want the value against the data in column A using vlookup without opening those files. actually there are more than 150files in row 1. in each file the lookup data is in column V. please help


thanks

Sudipta

 

[Luke M]

I was a little unsure still as to where the lookup data is, and which column contains the return value, but hopefully you can modify this to meet your needs.

[pre]

Sub CreateFormulas()
Dim lastCol As Long
Dim lastRow As Long
Dim formName As String
Dim foldName As String
Dim fullPath As String

'Name of folder where source files are at
'Be sure to include closing backslash
foldName = "D:DocumentsClosing"

Application.ScreenUpdating = False

'Define the boundaries of worksheet
With ActiveSheet
lastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

'Starting at column B (the 2nd column) and going to last col...
For i = 2 To lastCol
'Starting in row 2, and going to last row...
For j = 2 To lastRow
'Build the full folder & workbook path
fullPath = foldName & "[" & Cells(1, i).Value & ".xlsx]"

'Looking at end of formula, we're going to look in Sheet1
'Lookup value is in col A, return value is in col V
'Modify this as needed to match your situation
formName = "=VLOOKUP(A" & j & ",'" & fullPath & "Sheet1'!A:V,22,FALSE)"
Cells(j, i).Formula = formName
Next j
Next i
Application.ScreenUpdating = True
End Sub

[/pre]

 

[sudipto3003]

HEY LUKE, THANKS, THIS IS GREAT, I HAVE SUCESSFULLY COMPLETED MY JOB