generating formula with a formula

[Lisa Linard]

I want to create a formula in a worksheet that sums
column B in multiple csv files that are each
called "Tracking XXX (hits).csv" where XXX is different
for each file.
I put the XXX part in Col A of my worksheet and want to
generate the SUM formula by automatically inserting XXX
from Col A, then run the SUM formula to give me my total.
So if Col A contained "ABC" the final formula would look
like this:
=SUM('Filename ABC (hits).csv'!$B:$B)")
I've used CONCATENATE to create the formula:
=CONCATENATE("=SUM('Tracking ",A2," (hits).csv'!$B:$B)")
But the result just sits there as text. I can't figure
out how to get it to *work* as a formula. Any ideas?

 

[steve smallman]

Lisa,
Cell A1 = "ABC"

Cell B1 is your formula
=Sum(('Filename ' & a1 & ' (hits).csv'!$B:$B)

Should work.....

steve

 

[Tushar Mehta]

You don't need VBA for this. Check out XL's INDIRECT function.

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions