Index from Multiple Sheets/Tabs

[irwintan27]

Good morning, It's my first time and I need help figuring out the correct formula for what I needed to do. So I want a drop down list to where I can select a value and it will display all the data on that value from multiple sheets/tab. I was able to do that except I was only able to display data from the first sheet/tab. Attached is the excel file I'm working on, thanks in advance! Attached is my excel file.

[Glenn Kennedy]

Will each CESE report (e.g. 94-74286) appear every day?

What you have asked for is rather complicated, but if it's there every day, can be made much simpler...

[azumi]

If I may make a change on your workbook for easy setup for formula, I change the sheet name with the number

For CESE put this on A5 and copied down

=IFERROR(VLOOKUP($B$1,INDIRECT("'"&INDEX({1\2\3\4\5\6\7\8\9\10\11\12\13\14\15\16\17\18\19\20\21\22\23\24\25\26\27\28\29\30\31},AGGREGATE(15,6,1/(1/{1\2\3\4\5\6\7\8\9\10\11\12\13\14\15\16\17\18\19\20\21\22\23\24\25\26\27\28\29\30\31}*(COUNTIFS(INDIRECT("'"&{1\2\3\4\5\6\7\8\9\10\11\12\13\14\15\16\17\18\19\20\21\22\23\24\25\26\27\28\29\30\31}&"'!B3:B100"),$B$1)=1)),ROW(A1)))&"'!$B$3:$B$100"),1,0),"")

For Total M, put this on B5 and copied down

=IFERROR(VLOOKUP($B$1,INDIRECT("'"&INDEX({1\2\3\4\5\6\7\8\9\10\11\12\13\14\15\16\17\18\19\20\21\22\23\24\25\26\27\28\29\30\31},AGGREGATE(15,6,1/(1/{1\2\3\4\5\6\7\8\9\10\11\12\13\14\15\16\17\18\19\20\21\22\23\24\25\26\27\28\29\30\31}*(COUNTIFS(INDIRECT("'"&{1\2\3\4\5\6\7\8\9\10\11\12\13\14\15\16\17\18\19\20\21\22\23\24\25\26\27\28\29\30\31}&"'!B3:J100"),$B$1)=1)),ROW(B1)))&"'!$B$3:$J$100"),6,0),"")

For Total H Put this on C5 and copied down

=IFERROR(VLOOKUP($B$1,INDIRECT("'"&INDEX({1\2\3\4\5\6\7\8\9\10\11\12\13\14\15\16\17\18\19\20\21\22\23\24\25\26\27\28\29\30\31},AGGREGATE(15,6,1/(1/{1\2\3\4\5\6\7\8\9\10\11\12\13\14\15\16\17\18\19\20\21\22\23\24\25\26\27\28\29\30\31}*(COUNTIFS(INDIRECT("'"&{1\2\3\4\5\6\7\8\9\10\11\12\13\14\15\16\17\18\19\20\21\22\23\24\25\26\27\28\29\30\31}&"'!B3:M100"),$B$1)=1)),ROW(C1)))&"'!$B$3:$M$100"),9,0),"")

Hope this helps, and if not hope other member can help you

[irwintan27]

Will each CESE report (e.g. 94-74286) appear every day?

What you have asked for is rather complicated, but if it's there every day, can be made much simpler...

Not all CESE will appear on the report every day. Half of CESE will be reported if they were used that day.

If I may make a change on your workbook for easy setup for formula, I change the sheet name with the number

For CESE put this on A5 and copied down

=IFERROR(VLOOKUP($B$1,INDIRECT("'"&INDEX({1\2\3\4\5\6\7\8\9\10\11\12\13\14\15\16\17\18\19\20\21\22\23\24\25\26\27\28\29\30\31},AGGREGATE(15,6,1/(1/{1\2\3\4\5\6\7\8\9\10\11\12\13\14\15\16\17\18\19\20\21\22\23\24\25\26\27\28\29\30\31}*(COUNTIFS(INDIRECT("'"&{1\2\3\4\5\6\7\8\9\10\11\12\13\14\15\16\17\18\19\20\21\22\23\24\25\26\27\28\29\30\31}&"'!B3:B100"),$B$1)=1)),ROW(A1)))&"'!$B$3:$B$100"),1,0),"")

For Total M, put this on B5 and copied down

=IFERROR(VLOOKUP($B$1,INDIRECT("'"&INDEX({1\2\3\4\5\6\7\8\9\10\11\12\13\14\15\16\17\18\19\20\21\22\23\24\25\26\27\28\29\30\31},AGGREGATE(15,6,1/(1/{1\2\3\4\5\6\7\8\9\10\11\12\13\14\15\16\17\18\19\20\21\22\23\24\25\26\27\28\29\30\31}*(COUNTIFS(INDIRECT("'"&{1\2\3\4\5\6\7\8\9\10\11\12\13\14\15\16\17\18\19\20\21\22\23\24\25\26\27\28\29\30\31}&"'!B3:J100"),$B$1)=1)),ROW(B1)))&"'!$B$3:$J$100"),6,0),"")

For Total H Put this on C5 and copied down

=IFERROR(VLOOKUP($B$1,INDIRECT("'"&INDEX({1\2\3\4\5\6\7\8\9\10\11\12\13\14\15\16\17\18\19\20\21\22\23\24\25\26\27\28\29\30\31},AGGREGATE(15,6,1/(1/{1\2\3\4\5\6\7\8\9\10\11\12\13\14\15\16\17\18\19\20\21\22\23\24\25\26\27\28\29\30\31}*(COUNTIFS(INDIRECT("'"&{1\2\3\4\5\6\7\8\9\10\11\12\13\14\15\16\17\18\19\20\21\22\23\24\25\26\27\28\29\30\31}&"'!B3:M100"),$B$1)=1)),ROW(C1)))&"'!$B$3:$M$100"),9,0),"")

Hope this helps, and if not hope other member can help you

Ill check your modification and report back.

Thanks all for quick reply.