AUTOMATICALLY ADJUSTING SPREADSHEET FORMULAS AND/OR FORMATTING

• US 20160055139A1
• Filed: 12/17/2014
• Published: 02/25/2016
• Est. Priority Date: 08/22/2014
• Status: Active Grant

• Current Assignee: Smartsheet Inc.
• Original Assignee: Smartsheet Inc.
• Inventors: Creason, John D., Mader, Mark P., Maliska, Thomas P. Jr., Rucker, Erik
• Granted Patent: US 9,652,446 B2
• Time in Patent Office: Days
• US Class Current: 1/1
• CPC Class Codes: G06F 40/18 of spreadsheets form-filling G06F40/174

Abstract

In some embodiments, a computer-implemented spreadsheet management method is provided that automatically copies formatting and formulas from appropriate peer rows to an updated row. In some embodiments, the method automatically determines which peer rows, if any, should be used as the source of copied formatting and formulas. In some embodiments, the method automatically fixes formulas that are affected by the updated row in order to maintain consistency throughout the spreadsheet.

Claims

1. A computer-implemented method of automatically modifying a computerized information display, the method comprising:

detecting, by a computing device, that a trigger indicating an updated row in a spreadsheet has been activated;

reviewing, by the computing device, rows in the spreadsheet to determine if the updated row should be automatically modified; and

in response to identifying a peer row to be used to modify the updated row, modifying, by the computing device, the updated row based on the identified peer row.

2. The computer-implemented method of claim 1, wherein reviewing rows in the spreadsheet to determine if the updated row should be modified includes:

in response to determining that the updated row is between peer rows, identifying one of the peer rows as the identified peer row.

3. The computer-implemented method of claim 1, wherein reviewing rows in the spreadsheet to determine if the updated row should be modified includes:

in response to determining that the updated row is at the bottom of peer rows, identifying a preceding row to the updated row as the identified peer row.

4. The computer-implemented method of claim 3, further comprising, in response to determining that the updated row is at the bottom of peer rows:

modifying a range in a formula in the preceding row to include the updated row; and

not modifying a range in a formula in a following row to include the updated row.

5. The computer-implemented method of claim 1, wherein reviewing rows in the spreadsheet to determine if the updated row should be modified includes:

in response to determining that the updated row is at the top of peer rows, identifying a following row to the updated row as the identified peer row.

6. The computer-implemented method of claim 1, wherein reviewing rows in the spreadsheet to determine if the updated row should be modified includes:

in response to determining that the updated row is at the bottom of a single peer row, identifying a preceding row to the updated row as the identified peer row.

7. The computer-implemented method of claim 1, wherein reviewing rows in the spreadsheet to determine if the updated row should be modified includes:

in response to determining that the updated row is at the top of a single peer row, identifying a following row to the updated row as the identified peer row.

8. The computer-implemented method of claim 1, wherein the updated row is a new inserted row and the trigger to analyze the updated row is activated upon insertion of the updated row.

9. The computer-implemented method of claim 1, wherein the updated row is an edited existing row and the trigger to analyze the updated row is activated upon editing of the updated row, wherein editing of the updated row includes one or more of indenting the updated row, adding data to a cell in the updated row, a change in focus to or from a cell in the updated row, a save event, and a recalculation request.

10. The computer-implemented method of claim 1, wherein the trigger indicates a plurality of updated rows;

wherein the plurality of updated rows includes at least one updated row at a first hierarchical level and at least one updated row at a second hierarchical level; and

wherein the method further comprises;

reviewing rows in the spreadsheet at the first hierarchical level to determine if the at least one updated row at the first hierarchical level should be automatically modified;

in response to identifying a first peer row to be used to modify the at least one updated row at the first hierarchical level, modifying the at least one updated row at the first hierarchical level based on the identified first peer row;

reviewing rows in the spreadsheet at the second hierarchical level to determine if the at least one updated row at the second hierarchical level should be automatically modified; and

in response to identifying a second peer row to be used to modify the at least one updated row at the second hierarchical level, modifying the at least one updated row at the second hierarchical level based on the identified second peer row.

11. The computer-implemented method of claim 1, wherein modifying the updated row based on the identified peer row includes at least one of:

modifying formatting in the updated row based on formatting in the identified peer row, and

adding formulas to the updated row based on formulas in the identified peer row.

12. The computer-implemented method of claim 1, wherein modifying the updated row based on the identified peer row includes:

adding formulas to the updated row based on formulas in the identified peer row; and

updating row references in the formulas added to the updated row to reflect a position of the updated row.

13. The computer-implemented method of claim 1, wherein reviewing rows includes:

comparing at least two rows other than the updated row to each other; and

identifying the at least two rows as peer rows in response to determining that at least one of the row-level formatting, cell-level formatting, and formulas match in the at least two rows.

14. The computer-implemented method of claim 13, wherein comparing at least two rows other than the updated row includes comparing at least two rows other than the updated row at an indent level matching an indent level of the updated row;

and

wherein the at least two rows other than the updated row at the matching indent level are separated by at least one row at a different indent level.

15. A nontransitory computer-readable medium having computer-executable instructions stored thereon that, in response to execution by one or more processors of a computing device, cause the computing device to perform actions for automatically modifying tabular data, the actions comprising:

detecting, by the computing device, that a trigger indicating an updated row in the tabular data has been activated;

reviewing, by the computing device, rows in the tabular data to determine if the updated row should be automatically modified; and

in response to identifying a peer row to be used to modify the updated row, modifying, by the computing device, the updated row based on the identified peer row.

16. The computer-readable medium of claim 15, wherein reviewing rows in the tabular data to determine if the updated row should be automatically modified includes:

determining whether the updated row is between peer rows, and identifying one of the peer rows between which the updated row is located as the identified peer row in response to determining that the updated row is between peer rows;

in response to determining that the updated row is not between peer rows, determining whether the updated row is at the bottom of peer rows, and identifying a preceding row to the updated row as the identified peer row in response to determining that the updated row is at the bottom of peer rows;

in response to determining that the updated row is neither between peer rows nor at the bottom of peer rows, determining whether the updated row is at the top of peer rows, and identifying a following row to the updated row as the identified peer row in response to determining that the updated row is at the top of peer rows;

in response to determining that the updated row is neither between peer rows, nor at the bottom of peer rows, nor at the top of peer rows, determining whether the updated row is at the bottom of a single peer row, and identifying a preceding row to the updated row as the identified peer row in response to determining that the updated row is at the bottom of a single peer row; and

in response to determining that the updated row is neither between peer rows, nor at the bottom of peer rows, nor at the top of peer rows, nor at the bottom of a single peer row, determining whether the updated row is at the top of a single peer row, and identifying a following row to the updated row as the identified peer row in response to determining that the updated row is at the top of a single peer row.

17. The computer-readable medium of claim 15, wherein modifying the updated row based on the identified peer row includes at least one of:

modifying formatting in the updated row based on formatting in the identified peer row, and

adding formulas to the updated row based on formulas in the identified peer row.

18. A computing device configured to display, store, and manage tabular data by:

detecting activation of a trigger indicating that a row in a collection of tabular data has been updated;

reviewing rows other than the updated row in the collection of tabular data to identify a peer row to be used to modify the updated row; and

automatically modifying the updated row based on the identified peer row.

19. The computing device of claim 18, wherein reviewing rows other than the updated row includes:

comparing at least two rows other than the updated row to each other; and

identifying the at least two rows as peer rows in response to determining that at least one of the row-level formatting, cell-level formatting, and formulas match in the at least two rows.

20. The computing device of claim 19, wherein comparing at least two rows other than the updated row includes comparing at least two rows other than the updated row that are at an indent level matching an indent level of the updated row, and wherein the at least two rows other than the updated row at the matching indent level are separated by at least one row at a different indent level.