Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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
add comment

1 Answer 1

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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.