how automatically fill cells when update row from form ?

mohammad saaideh

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)

mohammad saaideh

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

Genevieve P.

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

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