How to write vba code for this

[lecet444]

as you can see the formulas here, the file name in all the formulas is the cell (column B) to the left. I would like to implement a vb code that would automatically fill out the sheet with the filenames in the formulas

Excel Workbook
	A	B	C	D	E	F	G
1	Date	PO NUMBER	TOTAL	abc	def	ghi	jkl
2	June 16, 2011	7418	$ 3,179.75
3	June 16, 2011	7419
4	June 16, 2011	7420
5	June 16, 2011	7421
6	June 16, 2011	7422
7	June 16, 2011	7423
8	June 16, 2011	7424
Raw Data

Excel 2010

Cell Formulas
Range	Formula
C2	='F:\Engineering\Michael\Excel\INVENTORY\PO\[7418.xls]PO'!$S$33
D2	=IF(ISNA(INDEX('F:\Engineering\Michael\Excel\INVENTORY\PO\[7418.xls]PO'!$C$16:$C$30,MATCH(D1,'F:\Engineering\Michael\Excel\INVENTORY\PO\[7418.xls]PO'!$D$16:$D$30,0))),"",(INDEX('F:\Engineering\Michael\Excel\INVENTORY\PO\[7418.xls]PO'!$C$16:$C$30,MATCH(D1,'F:\Engineering\Michael\Excel\INVENTORY\PO\[7418.xls]PO'!$D$16:$D$30,0))))
E2	=IF(ISNA(INDEX('F:\Engineering\Michael\Excel\INVENTORY\PO\[7418xls]PO'!$C$16:$C$30,MATCH(E1,'F:\Engineering\Michael\Excel\INVENTORY\PO\[7418.xls]PO'!$D$16:$D$30,0))),"",(INDEX('F:\Engineering\Michael\Excel\INVENTORY\PO\[7418.xls]PO'!$C$16:$C$30,MATCH(E1,'F:\Engineering\Michael\Excel\INVENTORY\PO\[7418.xls]PO'!$D$16:$D$30,0))))
F2	=IF(ISNA(INDEX('F:\Engineering\Michael\Excel\INVENTORY\PO\[7418.xls]PO'!$C$16:$C$30,MATCH(F1,'F:\Engineering\Michael\Excel\INVENTORY\PO\[7418.xls]PO'!$D$16:$D$30,0))),"",(INDEX('F:\Engineering\Michael\Excel\INVENTORY\PO\[7418.xls]PO'!$C$16:$C$30,MATCH(F1,'F:\Engineering\Michael\Excel\INVENTORY\PO\[7418.xls]PO'!$D$16:$D$30,0))))
G2	=IF(ISNA(INDEX('F:\Engineering\Michael\Excel\INVENTORY\PO\[7418.xls]PO'!$C$16:$C$30,MATCH(G1,'F:\Engineering\Michael\Excel\INVENTORY\PO\[7418.xls]PO'!$D$16:$D$30,0))),"",(INDEX('F:\Engineering\Michael\Excel\INVENTORY\PO\[7418.xls]PO'!$C$16:$C$30,MATCH(G1,'F:\Engineering\Michael\Excel\INVENTORY\PO\[7418.xls]PO'!$D$16:$D$30,0))))

 

[lecet444]

i dont know where to begin wehter its a loop or what. I dont have any programming experience or excel for that matter, but I'm coming along

 

[gregtx81]

If I understand your situation correctly, you don't need VBA. You can use Excel's Indirect function to build a cell reference based on strings.

So C2, for example, would be

 =Indirect("'F:\Engineering\Michael\Excel\INVENTORY\PO\[" & B2 & ".xls]PO'!$S$33")

Do the top row for each column then just copy down.

 

[lecet444]

thank you, but the reason i need vba is because the list of po numbers is always going to be changing. so I need the vba to match the number of rows, as they change. the list of po numbers changes based on a code, to call filter the po numbers by date

 

[lecet444]

thank you, but the reason i need vba is because the list of po numbers is always going to be changing. so I need the vba to match the number of rows, as they change. the list of po numbers changes based on a code, to call filter the po numbers by date

 

[lecet444]

Good morning all,

I am still having trouble with this code, if anyone could be kind enough to look into it, that would be great.


thank you

 

[lecet444]

Hello,

Really need that code, its the final major part of my project

 

[Darren Bartrup]

It's only the file name you want? Not the path to?

Function FileNameOnly(rng As Range) As String
    Dim sFormula As String
    sFormula = rng.Formula
    FileNameOnly = Mid(sFormula, InStrRev(sFormula, "[") + 1, Len(sFormula))
    FileNameOnly = Mid(FileNameOnly, 1, InStr(FileNameOnly, "]") - 1)
End Function

This looks for the square brackets around the file name, and will only return the first file name found in a formula.

 

[Darren Bartrup]

My god, just used the Microsoft Excel Function Reference v4.0 book that's been sitting on my desk for the last six years.....

You could sent up a named range.
Call it something like 'CellFormula' and in the "Refers to:" box type

GET.FORMULA(A1)

This will return the formula of the referenced cell.
You can use this formula to extract the book reference:

=MID(CellFormula,FIND("[",CellFormula)+1,FIND("]",CellFormula)-FIND("[",CellFormula)-1)

Edit: May not be as fast as the VB code though as it references CellFormula 4 times.

 

[lecet444]

Hello, thank You very much

I will try this code asap. and The file path as well I needed, but I think i could figure that out. appreciate your input