5

I have a spreadsheet with approx twenty different sheets named by stock ticker. Because these are templates, the information in each sheet is found in the same cells. For example, the EPS for the current year is always in cell A55 regardless of the sheet.

I want to build a summary sheet that will pull data from the templates based on the tickers. So envision a summary sheet with tickers (also sheet names) down column A and formulas in columns B to reference the same position in each different sheet in the list.

I know I can build out one row and then simply copy it down and do Find/Replace for each sheet name/ticker, but it seems I should be able to automate the formulas so these know to look at column A, then go to the sheet with that name, then return the data that is referenced in the formula. This would make it much easier to add sheets/tickers as time went on. I have tried =INDIRECT without success.

How do I get the formulas to reference column A in the summary sheet so that these know in which template/sheet to look up the information?

pnuts
  • 58,317
  • 11
  • 87
  • 139
user3217303
  • 53
  • 1
  • 1
  • 3
  • Have you looked at the `Indirect()` function? You can research [Here On Office.Microsoft.Com](http://office.microsoft.com/en-us/excel-help/indirect-HP005209139.aspx) or [Here On CPeasrson.Com](http://www.cpearson.com/excel/indirect.htm) – user2140261 Jan 21 '14 at 01:31
  • I have and I'm obviously too dumb to understand it. I can't seem to make it work. – user3217303 Jan 21 '14 at 01:32

3 Answers3

5

Please try: 

=INDIRECT(A1&"!A55")

Regarding Alaa M's Comment and Xenix Chroptl's Answer, spaces in the sheet name are handled, though extra quotes are required:

=INDIRECT("'"&A1&"'!A55")
pnuts
  • 58,317
  • 11
  • 87
  • 139
  • 1
    THANK YOU!!! I had way too much in the formula based on another example I was using. I knew it had to be much simpler. Awesome! – user3217303 Jan 21 '14 at 01:39
  • As @binaryfunt said in [his answer](http://stackoverflow.com/a/21247639/900394), in `A1` should be written the name of the sheet you want to extract data from. Also, there **must not be any spaces** in the sheet name! – Alaa M. Dec 09 '16 at 20:53
1

EDIT:

This answer did not really help, but I will leave this for anyone in the future wondering how use the INIDRECT() formula, in a SUM() formula as an example. If you wanted to sum column B in Sheet1, then you would do something like this:

=SUM(INDIRECT(A1&"!B:B"))

The contents of cell A1 being "Sheet1", then drag and fill into the other rows

binaryfunt
  • 6,401
  • 5
  • 37
  • 59
  • Ah, it would be `=SUM(INDIRECT(A1&"!B:B"))` – binaryfunt Jan 21 '14 at 01:52
  • I was just using summation as an example, as I couldn't tell what formula was required in column B of the summary sheet. But anyway, you had already answered the question by the time I posted that! – binaryfunt Jan 21 '14 at 02:16
-1

Just stumbled across this while trying to solve a similar problem.

In case your tab titles include spaces or odd characters (such as hyphens), frame the tab reference (A1) in literal single quotes ("'"). Otherwise, you get #REF! errors when it tries to evaluate the reference.

=INDIRECT("'"&A1&"'!A55)

Xenix Chroptl
  • 1