# Dynamic Array Formula

#### jb

##### Member
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.

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:
Code:
``=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).

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:
Code:
``=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.