Forum Discussion
Table Entry Line
So I have suggested that excel add an 'ENTRY LINE' to tables. See that suggestion here: https://feedbackportal.microsoft.com/feedback/idea/ae0a8991-71fa-ed11-a81c-000d3ae5b6f4 I image it would loo...
Idea is interesting, but the script is unshared. Could you add it to the post or share by other way?
I thought you could/would find it in the file but good point I added it here too. I'm still learning what is shared in the workbook itself vs. lives online
it's shared through your OneDrive permissions, not with the file.
I upvoted and added few comments.
- I think here would be better place for discussion and would love to improve the script. This script is ideally only a temp solution as I hope they add something native. That said I'm pasting your comments here:
~~~~~~~~~~~~~~~~~~
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).I modified a bit for the single table not to add empty row and to skip cells if the table has formula in related column
/** * This script inserts the row above a table into the table. */ function main(workbook: ExcelScript.Workbook) { let wksht = workbook.getActiveWorksheet() let tbl = wksht.getTable("Table1") const newData = tbl .getHeaderRowRange() .getOffsetRange(-1, 0) if ( !newData .getValueTypes() .every(v => v[0] == ExcelScript.RangeValueType.empty) ) { tbl.addRow() const newRow = tbl .getRangeBetweenHeaderAndTotal() .getLastRow() const n = newRow.getColumnCount() const content = newRow.getFormulas() for (let i = 0; i < n; i++) { if( !content[0][i].toString().startsWith("=") ) { newRow .getCell(0, i) .copyFrom( newData.getCell(0, i) ) } } newData.clear(ExcelScript.ClearApplyTo.contents) } }