#### jb

##### Member

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.