Forum Discussion
Table Entry Line
~~~~~~~~~~~~~~~~~~
Idea is good. However
- shift() method gives us first created in that worksheet table, better to work with concrete table
- that doesn't work on filtered table
- it's better to keep fields in added row where the formula is expected blank and expand formula in added row as it is in related column. Perhaps even if not blank it's better to give priority for the fromula in table column
~~~~~~~~~~~~~~~~~~
as for shift(), i was having trouble implementing an Index and trying to make it general (i.e. you don't have to edit the script for every page so right now this is limited to or at least best if only 1 table is on the sheet. I am open to other suggestions. I was also considering looping through all tables on the sheet but that could also have issues. Maybe a combination of looping all tables and then checking the potential entry line for a particular formatting or something to 'mark' it as an entry line.
I will have to test it on a filtered table. not sure why it doesn't work but will see if I can find a work around
I hear what you are saying about the formula. this was created in a specific application I'm using it for and in that application it is actually better to have the formula on the input and the value in the table because it was looking up the highest previous/existing value already entered in the table. Although still possible in the table it is 'excessive' and once entered it is good to be locked as a value instead of a formula. that said, to do the other will also be more complicated as I think it will have to be done cell by cell instead of the whole row. Maybe a combination is possible using the 'format' idea above that cells with the right format are copied and other are not (i.e. default formula in table should auto-populate and not be over-written).
shift(). I don't like an idea to loop all tables in active worksheet. Perhaps it's better getTable("TableName") but all depends on scenario. If to loop when at least ignore empty row under the each table.
filtering. Didn't play with the script itself and don't know what the solution is. But believe it exists.
formula. Didn't test but I guess now we break auto-fill of the table formula. Perhaps we may check any row in the table, if formula exists in the column when push it, otherwise value from the upper row. Yes, that's cell by cell but it shall be not critical from the performance point of view, we work only with one row. Recursion allows not care about number of cells.
In general that's interesting task, thank you for initiating it. Will try to play with script as well when have bit more time. If any results will share.