Reference file: here

Hi everyone! For the longest time, I've been using a formula applied in column A of the test file to replicate the labels for different tasks. However, the problem with this is when someone else inserts a row, the formula is not applied on that row. We need to replicate the labels per row since we use these in creating summary tables and charts.

I've been trying to create an array formula to hopefully address the 'Insert row' issue, but still can't do it. I'm thinking it's because each current cell references to the previous cell, which is a bit tricky (or impossible to do?) in array formulas.

Any possible approach in doing this? Our users aren't usually Sheets-savvy, so hopefully we can keep the column A hidden while automatically replicating labels when new rows are inserted.

Thanks!

CodePudding user response：

If Excel: you can use tables (insert > table).

Beside a lot of other advantages one is that formulas automatically extend to new rows.

But you have to omit the empty rows. Instead you can use a conditional formatting that draws a line whenever the label changes.

CodePudding user response：

Just found out the solution from another forum:

=ArrayFormula(if(C2:C="","",lookup(row(A2:A),row(A2:A)/if(B2:B<>"",TRUE,FALSE),B2:B)))

Still trying to learn how this works step by step, but for now it's good.