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 :
Best 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
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
Answers
-
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
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!