how automatically fill cells when update row from form ?
HI
I am using ...INDEX({Hailo}, MATCH(MAX(COLLECT({Cumulative counter}, {Tech name}, @cell = [WTG 2]90)), {Cumulative counter}, 0)) .... function , to get last updated cell from form .
but if i didnt fill every cells in row the smartsheet will take now row with empty cell for ex :
row 1: name /age/gender
after get update from form and just update the age :
new row 1 : new name /age(empty)/gender (empty)
and the sheet what i take information to will show::
row1: new name /invalid data (bcz its empty from source)
Best Answer
-
Hi Genevieve P,
Thanks for answering i was kept trying until i find this formula :
=IFERROR(IF(ISBLANK(INDEX{Source Column} , MATCH(MAX(COLLECT({Cumulative counter}, {Tech name}, @cell = [WTG 1]89)), {Cumulative counter}, 0))), INDEX({Source Column}, MATCH(MAX(COLLECT({Cumulative counter}, {Tech name}, @cell = [WTG 1]89, {Source Column}, <>"")), {Cumulative counter}, 0)), INDEX({Source Column}, MATCH(MAX(COLLECT({Cumulative counter}, {Tech name}, @cell = [WTG 1]89)), {Cumulative counter}, 0))), "")
If sys find cell blank in the MAX , that mean no change on that cell in row , the change happend to another cell , so if its blank in new row get the last cell not blank .
and its work very good .
thanks you
Answers
-
If you just want to get rid of the error message, you could wrap the formula in an IFERROR statement like so:
=IFERROR(INDEX({Hailo}, MATCH(MAX(COLLECT({Cumulative counter}, {Tech name}, @cell = [WTG 2]90)), {Cumulative counter}, 0)), "No Match")
However it sounds like you want to return the previous entry if the most recent entry is giving you an error. In this case, you could use the LARGE function instead of the MAX function and say if there's an error, go to the second largest date instead of the current max date.
=INDEX({Hailo}, IFERROR(MATCH(MAX(COLLECT({Cumulative counter}, {Tech name}, @cell = [WTG 2]90)), {Cumulative counter}, 0), MATCH(LARGE(COLLECT({Cumulative counter}, {Tech name}, @cell = [WTG 2]90), 2), {Cumulative counter}, 0))
That said, if the cell is simply blank, I would expect your formula to return a blank cell versus an error, so this may not work. Let me know if you still need help! If so, it would be useful to see screen captures of your sheets, but please block out sensitive data.
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Hi Genevieve P,
Thanks for answering i was kept trying until i find this formula :
=IFERROR(IF(ISBLANK(INDEX{Source Column} , MATCH(MAX(COLLECT({Cumulative counter}, {Tech name}, @cell = [WTG 1]89)), {Cumulative counter}, 0))), INDEX({Source Column}, MATCH(MAX(COLLECT({Cumulative counter}, {Tech name}, @cell = [WTG 1]89, {Source Column}, <>"")), {Cumulative counter}, 0)), INDEX({Source Column}, MATCH(MAX(COLLECT({Cumulative counter}, {Tech name}, @cell = [WTG 1]89)), {Cumulative counter}, 0))), "")
If sys find cell blank in the MAX , that mean no change on that cell in row , the change happend to another cell , so if its blank in new row get the last cell not blank .
and its work very good .
thanks you
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!