Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have 10+ worksheets, with the same layouts, all containing data from different days jobs. I am trying to create a "SimpleSheet" that only pulls specific data from one days sheet in a nice layout. So far I am doing it manually:

A1 | ='DaySheet1'.A1
A2 | ='DaySheet1'.A2

What I would like to do is alter the functions to instead of where it says DaySheet say "pull the sheet I have defined in SimpleSheet cell B1"

B1 | *whatever sheet*
A1 | ='B1'.A1
A2 | ='B1'.A2
share|improve this question

Use Indirect.

Indirect allows you to use a string as a reference to a worksheet location. So, if the content of cell A1 is set to 'DaySheet', you can use the formula =Indirect(A1)!B1 and the result will be whatever is in cell B1 in the DaySheet sheet.

From the support page:

You can reference each attribute of the reference (workbook name, worksheet name, and cell reference) individually by using the INDIRECT function to create a user-defined dynamic reference with worksheet cell references as inputs.

EDIT: Might need a mention on how to deal with referencing the current cell.

To drill down from the sheet to the cell, you can either use a string reference (eg "A1") concatenated to the indirect reference, or you can use the address function to do it dynamically. Below is an example of both:

String:

=INDIRECT(CONCATENATE($A$1,"!","B1"))

Dynamic:

=INDIRECT(CONCATENATE($A$1,"!",ADDRESS(ROW(), COLUMN(), 4)))

The first is easiest where you don't copy from too many cells. More than a handful, and I'd go with the second option.

MS help on the Address function.

share|improve this answer
    
Just a small remark: references to other workbooks based on INDIRECT will work only in case workbook is opened. See more: stackoverflow.com/a/14788167/2043977 – Peter L. Feb 10 '13 at 17:35
    
A1 set to 'DaySheet'. B1 =indirect(A1) Parse Error. B1 =indirect("a1") returns 'DaySheet'. B1 =indirect("A1")!B1 Parse Error. =indirect("'DaySheet'!B1") Returns correct cell. =indirect("'DaySheet'")!B1 Parse Error. – user197060 Feb 11 '13 at 1:38
    
@user197060 edited – mcalex Feb 11 '13 at 2:31

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .