Need some quick help! I’m trying to reference cells from a collection of separate files without using names. I have a master file with an input cell in which you can enter the ticker symbol of a company. For each ticker symbol, there is a related model. For instance, say that I want to enter cell D15 of my Google model (named GOOG). The Excel syntax would be as follows: =’[GOOG.xls]Commodity Sensitivity’!D15 Is there a way to combine the input of the ticker symbol in my master file with the actual file name? The models are identical in format such that each cell references the same type of metric. For instance, cell D15 refers to the P/E ratio of each company. Rather than having to name each cell, is it possible to utilize my master file ticker input with the generic file cell reference? Thanks!!!
You could just copy and paste, and then use the replace feature to change the symbols
=concatenate("’[",A1,".xls]Commodity Sensitivity’!D15" Where A1 = the cell that has “GOOG” in it.
mossy, I thought of that function but I believe it only creates a text string that can’t actually reference a file. Am I thinking about this wrong?
google the INDIRECT function. I think this is what you’re trying to do.
It sounds like you’re trying to pull data from closed workbooks so INDIRECT will not help you here. I would write a simple macro to open the workbook and extract the data. If you want to get a little more complicated, somebody wrote a custom function that allows indirects to work with closed workbooks. Just google “harlan grove pull function”
that’s perfect! thanks a lot everyone.