How to fetch the data from the multiple sheets by using a correct formula

[Kumar@123]

Dear Friends,

I need your support to fetch the information to a consolidated sheet highlighted in yellow from the multiple sheets (N29 and 029).

Basically, I need to capture the landlord name and PAN mentioned for each of the respective employees on a consolidated sheet (the first sheet) by using Lookup or any other formula without macros or links. The original data set is very big, consisting of more than 200 sheets; hence, I have attached a sample sheet herewith.

If I knew the formula/logic, I would apply the same to the real data. I appreciate your kind support in this regard.

Please help me out. Thank you!

-Kumar

 

[Kumar@123]

Friends,

Awaiting for your response.

Kindly help me with a solution Thank you.

 

[bosco_yip]

Try,

1] In B2, formula copied down:

=IF($A2="","",INDIRECT($A2&"!N29"))

2] In C2, formula copied down:

=IF($A2="","",INDIRECT($A2&"!O29"))

 

[Kumar@123]

Hi Friend,

Thank you very much. This is worked out.