I have created different tabs/ sheets in excel each with a table. Each table has the same headers and has one column using the same drop-down list. I want to summarise all tables into a single table in a separate sheet based on the values from the drop-down menu. In my company I cannot use VBA. This post has answered most of my question: Consolidating Dynamic Named Ranges from Separate Sheets The answer came from Chris Nielsen:
=LET( data1,FILTER('Worksheet 1'!B:D,'Worksheet 1'!B:B<>""), data2,FILTER('Worksheet 2'!B:D,'Worksheet 2'!B:B<>""), rows1,ROWS(data1), rows2,ROWS(data2), cols1,COLUMNS(data1), rowindex,SEQUENCE(rows1+rows2), colindex,SEQUENCE(1,cols1), IF( rowindex<=rows1, INDEX(data1,rowindex,colindex), INDEX(data2,rowindex-rows1,colindex)) )
I am not a coder and have tried several solutions to add more worksheets.The code works apart from displaying the results of all tables (INDEX). It does do an accurate count but shows #value as a result in all cells.
This is my start code based on the information above for only two sheets and this works:
IF( rowindex<=rows1, INDEX(piarawaters1,rowindex,colindex), INDEX(roleystone1,rowindex-rows1,colindex)) )
These are my tries to add another sheet:
IF( rowindex<=rows1, INDEX(piarawaters1,rowindex,colindex), INDEX(roleystone1,rowindex-rows1,colindex) INDEX(armadalewungong1,rowindex-rows2,colindex)) )
IF( rowindex<=rows1, INDEX(piarawaters1,rowindex,colindex), INDEX(roleystone1,rowindex-rows1,colindex) INDEX(armadalewungong1,rowindex-(rows1+rows2),colindex)) )
IF( rowindex<=rows1, INDEX(piarawaters1,rowindex,colindex), INDEX(roleystone1,rowindex-rows1,colindex) INDEX(armadalewungong1,rowindex-rows1-rows2,colindex)) )
IF( rowindex<=rows1, INDEX(piarawaters1,rowindex,colindex), INDEX(roleystone1,rowindex-rows1,colindex) INDEX(armadalewungong1,rowindex-rows1+rows2,colindex)) )
I hope someone can help me :)
edit to add example
Cell C16 would be a drop-down list, the same as the status in the tables. The final table will then show only those with that status from all tables. It may not matter that the status is a drop-down.