yogi_Pull Data Into WorkBook2 From WorkBook1 From A Sheet Named As A Variable In This Target WorkBook2's Cell I1

                                           Google Spreadsheet   Post  #873
              Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Nov 17, 2012
user humbabba said : (http://productforums.google.com/forum/?zx=7ryvzevqsse0#!category-topic/docs/spreadsheets/tz1egG9RYjk)
IMPORTRANGE with sheet name as variable from first sheet
Hi,

I want to do this:

=IMPORTRANGE(D1,"Derrick!i2:i7")

Where Derrick is a variable from the first sheet, not the other workbook referenced in D1. I have a row of employee names:

Cecelia Derrick George

And I want to import data from another workbook for each. But I want the formula to be dynamic so it pulls the appropriate sheet name from the current sheet and looks for it on the correct sheet in another workbook.

Can't figure this out - any variable I substitute for Derrick ends up looking in the second workbook, not the current.

Any ideas?

---------------------------------------------------------------------------------------

following is an illustration of solution to the problem

this is the source WorkBook1:

and data is pulled into the target WorkBook2 from a sheet of WorkBook1 ... the sheet is named as a variable in the target file's cell I1