Returning cell values from other sheets by referencing sheet names from a list

excel excel-formula formula worksheet-function excel-indirect
user3217303 picture user3217303 · Jan 21, 2014 · Viewed 52.2k times · Source

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?

Answer

pnuts picture pnuts · Jan 21, 2014

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")

Related questions

Simulate string split function in Excel formula

I am trying to split a string in an excel formula, something like I can do in many programming languages, e.g. string words = "some text".split(' '); The problem is that I can't be sure that there is …

Read More
Retrieving Values From Excel Merged Columns

I'm doing a hlookup against a value that spans multiple columns. My data is similar to this: A B C D --------------------------- 1| Col1 Col2 2| x y z w 3| 4| In rows 3 and 4 (A3, B3, C3, D3, etc.), I'd like to put …

Read More
Count Unique values with a condition

In column A I have list of different names. In column B, I have values either 0 or 1. I want to get a count of all the unique names from column A which have 1 in column B. Using below array formula …

Read More