Index Collect/match formulas / automatically update

Paul_Arnaud

Hi everyone,

I'd like to have a formulas which reports what we call "Cost Center" from one/multiple sheet into a Year Planning sheet.

I need the formula to give me the "Cost Center" if a cell is matching the name of the collapse one (category) above it.

Source sheet (Master Sheet NL) :

Year Planning sheet :

My formula : =IFERROR(INDEX(COLLECT({Remote Surveillance Master NL Range CC}, {Remote Surveillance Master NL Range RS Quarter}, [Cost Centre]2), 1), "") for the 1st row, then =IFERROR(INDEX(COLLECT({Remote Surveillance Master NL Range CC}, {Remote Surveillance Master NL Range RS Quarter}, [Cost Centre]2), 2), "")

I added the IFERROR to make sure no data is display if there is nothing to see.

My question : because in the future, there might be some new cells added which will match the criteria, is there a way to show a range a cell that match the criteria without having to change everytime the row_index from the INDEX formula? I mean, right now I have 5 rows, but more will be needed in the future, which means row_index will have to change :

Genevieve P.

Hi @Paul_Arnaud

What I would do here is have a helper column to the left with just the number populated in it. Then you can reference that number in your formula instead of manually typing it in:

=IFERROR(INDEX(COLLECT({Remote Surveillance Master NL Range CC}, {Remote Surveillance Master NL Range RS Quarter}, [Cost Centre]2), [Number Column]@row), "") 

Then you could have a number in the top, parent row to COUNT how many rows meet your criteria in the other sheet, to make sure you have the same number of rows below:

=COUNTIFS({Remote Surveillance Master NL Range RS Quarter}, [Cost Centre]@row)

There wouldn't be a way to automatically create new rows based on this number, but it should give you a quick indicator if you should create a new row with the formula.

Let me know if that makes sense or if you'd like to see screen captures!

Cheers,

Genevieve

Genevieve P.

Hi @Paul_Arnaud

What I would do here is have a helper column to the left with just the number populated in it. Then you can reference that number in your formula instead of manually typing it in:

=IFERROR(INDEX(COLLECT({Remote Surveillance Master NL Range CC}, {Remote Surveillance Master NL Range RS Quarter}, [Cost Centre]2), [Number Column]@row), "") 

Then you could have a number in the top, parent row to COUNT how many rows meet your criteria in the other sheet, to make sure you have the same number of rows below:

=COUNTIFS({Remote Surveillance Master NL Range RS Quarter}, [Cost Centre]@row)

There wouldn't be a way to automatically create new rows based on this number, but it should give you a quick indicator if you should create a new row with the formula.

Let me know if that makes sense or if you'd like to see screen captures!

Cheers,

Genevieve

Paul_Arnaud

Hi @Genevieve P. ,

Thankf for your answer. I thought about this in the meantime. It's working !

I was more wondering to have, as you said, "an automatically new rows"if these rows were matching the criteria(s).

I'm not a huge fan of helper column, but sometimes there is no choice.

Anyway, thank you to take some of your time to look at my question.

Have a great day !