CODEWITHSUNDEEP
excelworksheet
user3647226
user3647226

Reputation: 11

Excel Dynamic Sheet Reference

I have total 4 worksheets in excel File.

1.Sheet1

2.Sheet2

3.Sheet3

4.Summary

screenshot

In Summary Sheet on A1 there is Sheet Name and in A1:A3 I have random different subjects. If I choose sheet2 in A1 the subjects value from Sheet2 should copy here. Similarly if choose sheet3 it should give me the subjects value entered in sheet3. I did this with Nested If statement. but the problem is of IF Nesting upto 64 levels. I tried to did this with indirect, index, Match, Vlookup but didn't success to get exact sheet and subject value. please help.

Upvotes: 1

Views: 205

Answers (1)

David
David

Reputation: 1232

I see from your screenshot you've been investigating the indirect function. Try entering the following formula into cell A3 on your Summary sheet:

=HLOOKUP(A2,INDIRECT(A1&"!A1:E2"),2,FALSE)

Note that this is Hlookup rather than V because your tables are horizontal. Hope this helps.

Upvotes: 1

Related Questions