CODEWITHSUNDEEP
excelvbaexcel-formula
Jonathan Pinlac
Jonathan Pinlac

Reputation: 113

Concatenating variables in excel cell reference call to another workbook

I'm building a report in excel that retrieves the sum of all rows in another workbook. These workbooks are organized as follows:

A:\FEB\2-16\Summary 2-16-15.xlsm  
A:\FEB\2-17\Summary 2-17-15.xlsm  
etc..

Using a cell formula like 

=SUM('A:\FEB\2-16\[Summary 2-16-15.xlsm]Data'!$B$2:$B$1000000)

I can get the data I want without opening the other workbooks, but I have to do this for each week of the month. So I want to change the formula to something like this. Where the date is a variable.

>=SUM('A:\FEB\&"TEXT(Data!$A31,"m-d")"&\[Summary&" TEXT(Data!$A31,"m-d-yy")"&.xlsm]Data'!$B$2:$B$1000000)

but this gives me a reference error. Converting the whole thing to a string gives me the proper string, but it doesn't actually reference the data i need. Is there a way to incorporate the date value into the path name without breaking functionality?

Upvotes: 0

Views: 431

Answers (1)

Sam
Sam

Reputation: 948

If you do it in vba you can have something like.

activecell.value = "=sum('A:\FEB\" & format(sheets("data").range(A31), "m-d") & "\[Summary " & format(sheets("data").range(A31), "m-d-yy") & "")"&.xlsm]Data'!$B$2:$B$1000000)"

Upvotes: 1

Related Questions