Index Match formula populating when form added, without cross sheet being populated
I have a sheet for division entry. When it is approved by the director a workflow copies the entire row to a master sheet, where the commissioner approves. All approvals are a drop down symbol.
When the commissioner approves, there is an index/match formula on the division sheet, indicating if commissioner approves or rejects.
This works just fine. But when a new row is added, either with a form or directly on the sheet, the new entry populates the commissioner approval the same as the prior row. Effectively populating prior to the commissioner approval or rejection being applied on the master sheet.
The formula being used is
=INDEX({FY24 Travel Plan - Master Commissioner Approval}, MATCH([ID Number (Automatic)]@row, {FY24 Travel Plan - Master ID Number}))
I've tried having the formula as a cell formula and a column formula.
EDITED TO ADD: I've also added ,"0") at the end as well, resulting in an #invalid Data message.
Any suggestions?
Answers
-
I might have solved this by using an index collect formula and including an iferror statement.
New formula is:
=IFERROR(INDEX(COLLECT({FY24 Travel Plan - Master Commissioner Approval}, {FY24 Travel Plan - Master ID Number}, [ID Number (Automatic)]@row), 1), "No Match Found")
When adding rows to the division sheet, the commissioner approval column is no longer pre-populated. New rows now indicate "No Match Found".
It could be that an iferror would have resolved, without using index collect. I don't really have time to test, need to move forward!
-
Thanks for sharing your solution! 🙂
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!