Array Formulas fail when inserting a new Row

[Sir Lurkalot]

Hi I've used this forum as a knowledge base for a while now however this is my first post so I'll try to include enough information.

I've created a spreadsheet that reports weekly information for my company on tab#1 whereupon the data input from the end of each week is entered on tab#2 and all of my formulas are on tab#3 hidden.

Since some weeks have services that aren't completed I made dynamic ranges using array formulas for the graphs on tab#1 so the graph labels that have zero values are hidden and showing only the services sold during that week.

My problem is when I try to insert a new row on tab#2 (company introduces a new service to customers) all of my array formulas on tab#3 fail, graphs go beserk - even changing set bar colours and losing data labels. Reapplying the formula from scratch doesn't seem to work either.

{=IFERROR(INDEX(Input!A$4:A$504,SMALL(IF(Input!C$4:C$504=$E$34,ROW(Input!A$4:A$504)-ROW(Input!A$4)+1),ROWS(A$4:A5))),0)}

Formulas on Tab#3
Input = Tab#2
A = Services
C = Numbers 1,2,3,4,5, etc for separation of service departments
E = Service Department names

C & E columns are so the formula only lists Services for a particular department when choosing which data to show on the graphs on tab#1

Not sure what to do except rebuild the entire thing again from the beginning but this time leave a whole bunch of empty rows on tab#2 to add new services into.

 

[NdNoviceHlp]

"A5" maybe should be A$5. Maybe U should use named ranges in your formula. Set up the named ranges to refer dynamically to the changing input. HTH. Dave

 

[Chrisdontm]

It appears that you need your ranges defined with the OFFSET function...
OFFSET starts away from the range, and describes how to get to it, and then describes the size of the range.
That way you could add in a row and it wont mess up the range reference...

I tried modifying your formula, but I am not at home and cannot test it.
If it doesn't work, then it shouldn't be too far off, I would have to come back to it in a few days...
You may be able to tweak it yourself. Also try it on a copy of the document and not on the actual document.
Happy Thanksgiving...

{=IFERROR(INDEX(OFFSET(Input!$A$1,3,,501,1),SMALL(IF(OFFSET(Input!$A$1,3,2,501,1)=$E$34,ROW(OFFSET(Input!$A$1,3,,501,1))-ROW(OFFSET(Input!A$1,3,,1,1))+1),ROWS(A$4:A5))),0)}

 

[Sir Lurkalot]

{=IFERROR(INDEX(OFFSET(Input!$A$1,3,,501,1),SMALL(IF(OFFSET(Input!$A$1,3,2,501,1)=$E$34,ROW(OFFSET(Input!$A$1,3,,501,1))-ROW(OFFSET(Input!A$1,3,,1,1))+1),ROWS(A$4:A5))),0)}

Works well so far! I'll have to adapt it to some other formulas that also rely on the previous ranges but are slightly different. I'd only learned about dynamic ranges and how they could be used so I'll give it a crack to better my own understanding.

Will reply once I've applied and tested it fully and let you know if this remains stable when inserting new rows.

 

[Sir Lurkalot]

Champion

Have applied similar offset fixes to other formulas on the rest of tab#3 and tested it out with some fictional data.