Gurus,

  I am pretty much a novice to Excel / VBA Programmming, so please pardon my ignorance. I' trying to figure out what formula to use in the following scenario:

Cell - Sheet1!B1
the formula should chain through the references Sheet1!A1 -> Sheet2!A1 -> Sheet3!B1 -> extract the column (B in this case) -> lookup the corresponding cell in row 2 (Sheet3!B2) and display its value in Sheet1!B1.

Sheet1 >

Sheet1!A1 =Sheet2!A1
Sheet1!B1 ????

Sheet2 >

Sheet2!A1
=Sheet3!B1

Sheet 3>

Sheet3!A1 = 'Employee Name'
Sheet3!A2 = 'Tom Burke'
Sheet3!B1 = 'Employee ID'
Sheet3!B2 = 103416

Any suggestion would be very helpful.

TIA,
Shantanu

Don't see what your problem is :-

Sheet1!B1 =Sheet2!B1
or
Sheet1!B1 =Sheet3!B1

-----------------------
Regards BrianB
Most problems occur from starting at the wrong place.
Use a cup of coffee to make Windows run faster.
It is easy until you know how.

Hello Brian,

  Thanks for your response. My apologies. I think I made an error that led to the confusion.

  let me correct & rephrase my query.

Corrected >

Sheet1 refers to data in Sheet3 through references in Sheet2. The formula that I am looking for Sheet1!B1 does the following: It'll chain through the references in Sheet2!A1 till it reaches an absolute value 'Employee Name' (in Sheet3!A1), pick the corresponding value in Sheet3!B1 and display in Sheet1!B1.

  Why I need this? Say, now I change the reference in Sheet2!A1 to =Sheet3!A2 (from =Sheet3!A1). Sheet1!A1 automatically changes from 'Employee Name' to 'Tom Burke'. The formula in Sheet1!B1 will automatically cause the corresponding employee # to be displayed 103416



Sheet1 >

Sheet1!A1 =Sheet2!A1
Sheet1!B1 ????

Sheet2 >

Sheet2!A1 =Sheet3!A1

Sheet 3>

Sheet3!A1 = 'Employee Name'
Sheet3!A2 = 'Tom Burke'
Sheet3!B1 = 'Employee ID'
Sheet3!B2 = 103416

Thanks,
Shantanu.