Dynamic Array Formula

[jb]

Dear Helpers,

I have used following array formula for calculation in my excel sheet and it works fine.

cell e3 has formula {=IF($B3<>"",IF(SUMPRODUCT(COUNTIF(INDIRECT("'"&$A$101:$A$120&"'!$b$127:$b$132"),$B3))>0,E$2,""),"")}
cell f3 has formula {=IF($B3<>"",IF(SUMPRODUCT(COUNTIF(INDIRECT("'"&$A$101:$A$120&"'!$b$147:$b$152"),$B3))>0,F$2,""),"")}
cell g3 has formula {=IF($B3<>"",IF(SUMPRODUCT(COUNTIF(INDIRECT("'"&$A$101:$A$120&"'!$b$167:$b$172"),$B3))>0,G$2,""),"")} and so on for 8 continuous cells.

After 8 cells, $b$127:$b$132 will become $c$127:$c$132, $b$147:$b$152 will become $c$147:$c$152 and so on.
After 8 cells it will be d with same range repeated.

Now, A101 to A120 contains various sheet names of same excel workbook. But sometimes, I need to either add some sheets / delete some sheets. In that situation, I need to edit multiple formulas manually.

I want to convert this formulas in such a way that
1. Sheet range A101 to A120 will be created using 120 value comes from a cell as 101 is starting value and it is fixed
2. b127 to b32, b147 to b152 if can be made dynamic
3. after every 8 cell, b will become c, d, e and so on for every 8 cells

So that formula editing task will become very easy.

 

[p45cal]

A101 to A120 contains various sheet names of same excel workbook. But sometimes, I need to either add some sheets / delete some sheets. In that situation, I need to edit multiple formulas manually.

Convert the cells with the sheet names in into a proper Excel table. If you name that table, for example, SheetsTbl and give it the header SheetList then your formula becomes:

=IF($B3<>"",IF(SUMPRODUCT(COUNTIF(INDIRECT("'"& SheetsTbl[SheetList] &"'!$b$127:$b$132"),$B3))>0,E$2,""),"")

Then when you add/remove sheet names, make sure the table encompasses all the sheet names (it should do this automatically).

Re your points 2 and 3, I'll have a think but it would be VERY helpful if you could attach a file (simplified but still realistic).

 

[jb]

Convert the cells with the sheet names in into a proper Excel table. If you name that table, for example, SheetsTbl and give it the header SheetList then your formula becomes:

=IF($B3<>"",IF(SUMPRODUCT(COUNTIF(INDIRECT("'"& SheetsTbl[SheetList] &"'!$b$127:$b$132"),$B3))>0,E$2,""),"")

Then when you add/remove sheet names, make sure the table encompasses all the sheet names (it should do this automatically).

Re your points 2 and 3, I'll have a think but it would be VERY helpful if you could attach a file (simplified but still realistic).

Thank you for your help. I tried and got solution for point 2 and 3. Thank you again.