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

  • mohammad saaideh
    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

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @mohammad saaideh

    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

  • mohammad saaideh
    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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!