0

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?

Improve this question
2
  • did you try to "load" the variables into temp cells? and later do the sum.
    – Deian
    Commented Apr 24, 2015 at 14:56
  • I do not think you can do it the way you are trying with an excel formula like that. You could do it in VBA though
    – Sam
    Commented Apr 24, 2015 at 15:15

1 Answer 1

Reset to default
1

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)"
Improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.