Index Collect/match formulas / automatically update

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 :


Tags:

Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    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

Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    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

  • 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 !

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!