Indirect alternative for external workbook

[jimmisavage]

Hi, I'm struggling with an alternative here.

Cells A3:A50 has a list of workbook names in a specific location (this auto updates when a workbook is added or removed) which is where i want to pull my data from - from sheet 2 column B. Each workbook is of the same layout, which makes it a little easier.

For ease of explanation lets say I have Book1.xlsx, Book2.xlsx and Book3.xlsx saved at C://documents/test and I want to count the number of 'x''s in cell B15 and show the result in cell C1.
The tricky part is the number of files in the folder will change so it needs to be a dynamic formula that always looks in every file in the folder.

*an indirect formula will work but obviously requires all workbooks to be open - this is not possible, as there would be 100's of workbooks in the folder.
**also, we're using excel 2007 so i can't use a power query and VBA isn't a viable option.

Any help would be appreciated.

 

[James006]

May be before going to 2'000, you should restrict your initial test to 20 ...

Sub LoopGetValues()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim i As Long
For i = 2 To 2000
    Cells(i, 3) = GetValue(Cells(i, 1), Cells(i, 2), ["Sheet2"], Cells(i - 1, 3))
Next i
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = False
End Sub

Hope this will help

 

[jimmisavage]

May be before going to 2'000, you should restrict your initial test to 20 ...

Sub LoopGetValues()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim i As Long
For i = 2 To 2000
    Cells(i, 3) = GetValue(Cells(i, 1), Cells(i, 2), ["Sheet2"], Cells(i - 1, 3))
Next i
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = False
End Sub

Hope this will help

Thanks James. This seems to delete the file location in column A and then it throws an error. It seems to have added 'File not found.' to column C though, so it's started doing something.

 

[jasonb75]

I didn't think that this method would be suitable based on the requirements set out in post #1 , but given the problems encountered I'm going to suggest it as an alternative anyway.

This method creates an indirect refernce as a text string, then converts it to a simple direct reference. The downside is that once this is done, the reference is no longer indirect so it will not update if you change any of the address components, i.e. the contents of $A2, $B2 or C$1, etc.

Based on post #39 ,

="='"&$A2&IF(RIGHT($A2,1)="","","")&$B2&"Sheet2'!"&C$1

Copy the formula above, Select C2:AE2000 in your sheet, press f2, Ctrl + v, then Ctrl + Enter to fill the table with the formula using relative references.
Press Ctrl + c, then Ctrl + Alt + v, v, Enter to copy and paste special - values.

Press Ctrl + h to open the find and replace box, enter an = sign in both boxes, then click replace all.

 

[jimmisavage]

I didn't think that this method would be suitable based on the requirements set out in post #1 , but given the problems encountered I'm going to suggest it as an alternative anyway.

This method creates an indirect refernce as a text string, then converts it to a simple direct reference. The downside is that once this is done, the reference is no longer indirect so it will not update if you change any of the address components, i.e. the contents of $A2, $B2 or C$1, etc.

Based on post #39 ,

="='"&$A2&IF(RIGHT($A2,1)="","","")&$B2&"Sheet2'!"&C$1

Copy the formula above, Select C2:AE2000 in your sheet, press f2, Ctrl + v, then Ctrl + Enter to fill the table with the formula using relative references.
Press Ctrl + c, then Ctrl + Alt + v, v, Enter to copy and paste special - values.

Press Ctrl + h to open the find and replace box, enter an = sign in both boxes, then click replace all.

Hi Jason,
Thanks for looking at this again but i think there will be a problem because more and more files will be added to the folder so the file names in column B will change - there are currently only 50 files (i've left 1950 empty which will populate when new files are added) so I assume that means this new formula won't work?

 

[jimmisavage]

However, I can create a macro to follow each step when the file is opened - therefor updating it each time.
I'll give it a go, thank you!

 

[jasonb75]

To make it work in such situations, you would need to re-create the formulas to match the changes by repeating the process.

One of your earlier replies implied that you might already have a macro in place which is adding the file paths / names as new files are created, if that is the case then it should be possible to modify that in order to create the formulas at the same time.

 

[jimmisavage]

To make it work in such situations, you would need to re-create the formulas to match the changes by repeating the process.

One of your earlier replies implied that you might already have a macro in place which is adding the file paths / names as new files are created, if that is the case then it should be possible to modify that in order to create the formulas at the same time.

I'm not using vba to list the files - I'm using =IFERROR(INDEX(listFiles,ROW(B1))&IF(TODAY()=TODAY(),""),"") where B1 contains the file location finishing with *
It seems to work well.

This seems to be working so i'll test it further to make sure it's working fine.

Thank you all for the help! I'm so pleased its working