Excel counter multiple tabs

[dygitaldude]

[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 500"]
<tbody>[TR]
[TD]Order (A)[/TD]
[TD]Task (B)[/TD]
[TD]Status (C)[/TD]
[TD]Designated to (D)[/TD]
[TD]Completion Order (E)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]x.a[/TD]
[TD]Pending[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]x.b[/TD]
[TD]completed[/TD]
[TD]John[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 500"]
<tbody>[TR]
[TD]3[/TD]
[TD]x.c[/TD]
[TD]completed[/TD]
[TD]John[/TD]
[TD]2[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Hello geniuses,

I'm working with an excel workbook with multiple tabs. How would I insert name in Col 'D' and Count in Col 'E' once the status reads completed. And the counter will have to increment workbook wide since I'll be working in multiple tabs. For example if Order 2 is the first one to be completed it will have completing order 1, assume there is a next tab of similar 3 rows, and Order x.d is completed it will have completed order 2. And so on...

Any help is much appreciated.

Thanks

 

[FDibbins]

Here is 1 approach to this.

1. Create a list of all your sheet names. You could do this manually, or with the following UDF (user-defined function). However, because the UDF is a macro, you will need to enable macros and save as .xlsb

To get a list of sheet names
Create a range name (I called mine Sheetnanes
Refers to: =REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),"")

Then put this in a cell and copy down (started in A2)...
=IFERROR(INDEX(Sheetnames,ROWS($A$2:A2)),"")

If you want to be able to click the cell and go to that worksheet, change that formula to this...
=IFERROR(HYPERLINK("#"&"'"&INDEX(Sheetnames,ROWS($A$2:A2))&"'!A1",INDEX(Sheetnames,ROWS($A$2:A2))),"")

Then to do the count, put this where you need the answer to be...
=SUMPRODUCT(SUMIF(INDIRECT("'"&sheetnames&"'!T6:T1000"),C7,INDIRECT("'"&sheetnames&"'!M6:M1000")))
adjust me ranges (bolded) as needed to suite your data

 

[dygitaldude]

Here is 1 approach to this.

1. Create a list of all your sheet names. You could do this manually, or with the following UDF (user-defined function). However, because the UDF is a macro, you will need to enable macros and save as .xlsb

To get a list of sheet names
Create a range name (I called mine Sheetnanes
Refers to: =REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),"")

Then put this in a cell and copy down (started in A2)...
=IFERROR(INDEX(Sheetnames,ROWS($A$2:A2)),"")

If you want to be able to click the cell and go to that worksheet, change that formula to this...
=IFERROR(HYPERLINK("#"&"'"&INDEX(Sheetnames,ROWS($A$2:A2))&"'!A1",INDEX(Sheetnames,ROWS($A$2:A2))),"")

Then to do the count, put this where you need the answer to be...
=SUMPRODUCT(SUMIF(INDIRECT("'"&sheetnames&"'!T6:T1000"),C7,INDIRECT("'"&sheetnames&"'!M6:M1000")))
adjust me ranges (bolded) as needed to suite your data

Thank you kindly.

 

[FDibbins]

Happy to help