Global Update to add a row (or other ideas)?

Protonsponge

Hello Community - I have a blue print published by control centre and in one of my sheets I have a list of 119 countries detailed in [Country/Region]. I use this column and [Country Code] in INDEX/COLLECT formulas in my work flow and the love guys in the demand team like to chuck in new countries every now and then…

I need to add 7 new countries in my list, however I have this sheet currently have this sheet provisioned >50 times. I was hoping for use of Global Updates to add in my new rows, but I think I can only add a column with this feature?

Would anyone have any advice/guidance on how best to update my countries column going forward other than large cup of coffee, open sheet, insert rows, copy, paste, repeat!

Protonspounge

Paul Newcome

It will take some work, but my suggestion would be to shift the reference sheet to the portfolio level instead of the project level.

From my understanding, you are currently provisioning this sheet with every single project. My suggestion would be to have this sheet live outside of the project and reference it in your blueprint. This way any updates that need to be made to the list are made in a single sheet as opposed to having to manage this on every single project.

Granted this does involve a lot of work. You would first create the sheet at the portfolio level then have to go into every project sheet and update all of the cross sheet references, but the up front work would be offset by the fact that you wouldn't have to keep touching every project every time this sheet needs updated.

Protonsponge

Hello @Paul Newcome

Thank you very much for the reply, appreciate it. I have the method you suggested in place for other sheets in my build but I overlooked this one. I was hoping to use the Global Update to avoid a reverse engineer/re-build but I think you are right I need to separate this piece out of the blueprint. A good bit of work but it should set me up for the future,

Thank you, I am grateful!

Louis

Paul Newcome

Happy to help. 👍️

Andrée Starå

Global Update to add a row (or other ideas)?

Protonsponge

Jun 4, 2024

Hello Community - I have a blue print published by control centre and in one of my sheets I have a list of 119 countries detailed in [Country/Region]. I use this column and [Country Code] in INDEX/COLLECT formulas in my work flow and the love guys in the demand team like to chuck in new countries every now and then…

I need to add 7 new countries in my list, however I have this sheet currently have this sheet provisioned >50 times. I was hoping for use of Global Updates to add in my new rows, but I think I can only add a column with this feature?

Would anyone have any advice/guidance on how best to update my countries column going forward other than large cup of coffee, open sheet, insert rows, copy, paste, repeat!

Protonspounge

https://us.v-cdn.net/6031209/uploads/H8OBYM96MQMF/image.png

---

View Post

Hi,

I hope you're well and safe!

Do you have an auto number column in the flag sheets or something else that would be populated on all rows (even those without any flags added)?

Be safe, and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅ Did my comment(s) help/answer your question or solve your problem? Please support the Community and me by marking it - Insightful 💡- Vote Up ⬆️ - Aweseome ❤️ - or/and as the accepted answer. It will make it easier for others to find a solution or help to answer! I appreciate it, thanks!

Protonsponge

@Andrée Starå - interesting, sounds like you have an idea 🧐

I have a number of helper columns - Level, a few manual check boxes that I could repurpose… I could always drop in a new column via global updates if you have a work around?

Andrée Starå

Protonsponge

Jun 6, 2024

@Andrée Starå - interesting, sounds like you have an idea 🧐

I have a number of helper columns - Level, a few manual check boxes that I could repurpose… I could always drop in a new column via global updates if you have a work around?

Excellent!

Yes, I do! 😉

What I sometimes do in my client solutions, especially when I migrate from something and need to recreate it in the Control Center or something similar, and also when I know that the structure of the sheet will always stay the same, but the client might need to be able to add rows, is that I add an auto number or multiple sections with specific IDs, and then those can be used to add formulas as needed and similar.

Depending on your structure, you might be able to add a helper column or use a report to add a specific ID and then do some Global Updates to update with the new flags.

Make sense?

✅ Remember! Did my comment(s) help/answer your question or solve your problem? Please support the Community and me by marking it - Insightful 💡- Vote Up ⬆️ - Aweseome ❤️ - or/and as the accepted answer. It will make it easier for others to find a solution or help to answer! I appreciate it, thanks!

Protonsponge

@Andrée Starå - Thank you for the reply, very grateful… but I have to apologies the penny isn't dropping for me - 🤪

What I would like to achieve would be the ability to add it a new row to a spreadsheet that contains lots of rows already and in my [Country/Region]column be able to add a "new country"… perhaps mid-way through my list of countries. Can I leverage a unique ID column split my table at a certain row? 🤞🤞🤞