Automatically adjusting spreadsheet formulas and/or formatting

• US 9,652,446 B2
• Filed: 12/17/2014
• Issued: 05/16/2017
• 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, Jr., Thomas P., Rucker, Erik
• Primary Examiner(s): Tsui, Wilson
• Application Number:
  US14/574,003
• Publication Number:
  US 20160055139A1
• Time in Patent Office: 881 Days
• 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 has been activated, wherein the trigger indicates an updated row in a spreadsheet, and wherein the updated row is at a first indent level;

identifying, by the computing device, a set of peer rows of the updated row, wherein identifying the set of peer rows of the updated row comprises;

determining at least two existing rows of the spreadsheet other than the updated row that are also at the first indent level and are separated from each other by at least one row at a different indent level;

comparing at least one of row-level formatting, cell-level formatting, and formulas of the at least two existing rows to each other; and

in response to determining that at least one of the row-level-formatting, cell-level formatting, and formulas of the at least two existing rows match, designating the at least two existing rows as an identified set of peer rows of the updated row; and

modifying, by the computing device, the updated row based on at least one of a formula or formatting from the identified set of peer rows of the updated row.

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

3. The computer-implemented method of claim 1, wherein the updated row is an edited existing row and the trigger 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.

4. The computer-implemented method of claim 1, wherein modifying the updated row based on at least one of a formula or formatting from the identified set of peer rows of the updated row includes at least one of:

modifying formatting in the updated row based on formatting in a row of the identified set of peer rows, and

adding formulas to the updated row based on formulas in a row of the identified set of peer rows.

5. The computer-implemented method of claim 1, wherein modifying the updated row based on at least one of a formula or formatting from the identified set of peer rows of the updated row includes:

adding formulas to the updated row based on formulas in a row of the identified set of peer rows; and

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

6. The method of claim 1, further comprising:

detecting, by the computing device, that a second trigger has been activated, wherein the second trigger indicates a second updated row in the spreadsheet;

identifying, by the computing device, a set of peer rows of the second updated row, wherein identifying the set of peer rows of the second updated row includes designating at least one existing row as an identified set of peer rows of the second updated row; and

modifying, by the computing device, the second updated row based on at least one of a formula or formatting from the identified set of peer rows of the second updated row.

7. The computer-implemented method of claim 6, wherein identifying the set of peer rows of the second updated row includes comparing at least one of row-level formatting, cell-level formatting, and formulas of a preceding row of the second updated row and a following row of the second updated row;

and

wherein designating at least one existing row as the identified set of peer rows of the second updated row includes, in response to determining that at least one of the row-level formatting, cell-level formatting, and formulas of the preceding row and the following row match, designating the preceding row and the following row as the identified set of peer rows of the second updated row.

8. The computer-implemented method of claim 6, wherein identifying the set of peer rows of the second updated row includes comparing at least one of row-level formatting, cell-level formatting, and formulas of a preceding row of the second updated row and a row above the preceding row of the second updated row;

and

wherein designating at least one existing row as the identified set of peer rows of the second updated row includes, in response to determining that at least one of the row-level formatting, cell-level formatting, and formulas of the preceding row and the row above the preceding row match, designating the preceding row and the row above the preceding row as the identified set of peer rows of the second updated row.

9. The computer-implemented method of claim 8, further comprising, in response to determining that at least one of the row-level formatting, cell-level formatting, and formulas of the preceding row and the row above the preceding row match:

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

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

10. The computer-implemented method of claim 6, wherein identifying the set of peer rows of the second updated row includes comparing at least one of row-level formatting, cell-level formatting, and formulas of a following row of the second updated row and a row below the following row of the second updated row;

and

wherein designating at least one existing row as the identified set of peer rows of the second updated row includes, in response to determining that at least one of the row-level formatting, cell-level formatting, and formulas of the following row and the row below the following row match, designating the following row and the row below the following row as the identified set of peer rows of the second updated row.

11. The computer-implemented method of claim 6, wherein designating at least one existing row as the identified set of peer rows of the second updated row includes:

in response to determining that a preceding row of the second updated row has formatting or formulas to be copied or applied to the second updated row, and that either the row above the preceding row is blank, the row above the preceding row is empty, or the preceding row is the first row in the spreadsheet, designating the preceding row as the set of peer rows of the second updated row.

12. The computer-implemented method of claim 6, wherein designating at least one existing row as the identified set of peer rows of the second updated row includes:

in response to determining that a following row of the second updated row has formatting or formulas to be copied or applied to the second updated row, and that either the row below the following row is blank, the row below the following row is empty, or the following row is the last row in the spreadsheet, designating the following row as the set of peer rows of the second updated row.

13. 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 has been activated, wherein the trigger indicates an updated row in a spreadsheet, and wherein the updated row is at a first indent level;

identifying, by the computing device, a set of peer rows of the updated row, wherein identifying the set of peer rows of the updated row comprises;

determining at least two existing rows of the spreadsheet other than the updated row that are also at the first indent level and are separated from each other by at least one row at a different indent level;

comparing at least one of row-level formatting, cell-level formatting, and formulas of the at least two existing rows to each other; and

in response to determining that at least one of the row-level-formatting, cell-level formatting, and formulas of the at least two existing rows match, designating the at least two existing rows as an identified set of peer rows of the updated row; and

modifying, by the computing device, the updated row based on at least one of a formula or formatting from the identified set of peer rows of the updated row.

14. The computer-readable medium of claim 13, wherein the actions further comprise:

detecting, by the computing device, that a second trigger has been activated, wherein the second trigger indicates a second updated row in the spreadsheet;

identifying, by the computing device, a set of peer rows of the second updated row, wherein identifying the set of peer rows of the second updated row includes designating at least one existing row as an identified set of peer rows of the second updated row; and

modifying, by the computing device, the second updated row based on at least one of a formula or formatting from the identified set of peer rows of the second updated row.

15. The computer-readable medium of claim 14, wherein identifying a set of peer rows of the second updated row includes:

determining whether the second updated row is between peer rows by determining whether at least one of row-level formatting, cell-level formatting, and formulas of a preceding row of the second updated row and a following row of the second updated row match, and designating the preceding row of the second updated row and the following row of the second updated row as the identified set of peer rows in response to determining that the second updated row is between peer rows;

in response to determining that the second updated row is not between peer rows, determining whether the second updated row is at the bottom of peer rows by determining whether at least one of row-level formatting, cell-level formatting, and formulas of a preceding row of the second updated row and a row above the preceding row of the second updated row match, and designating the preceding row to the second updated row as the identified set of peer rows in response to determining that the second updated row is at the bottom of peer rows;

in response to determining that the second updated row is neither between peer rows nor at the bottom of peer rows, determining whether the second updated row is at the top of peer rows by determining whether at least one of row-level formatting, cell-level formatting, and formulas of a following row of the second updated row and a row below the following row of the second updated row match, and designating the following row to the second updated row as the identified set of peer rows in response to determining that the second updated row is at the top of peer rows;

in response to determining that the second 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 by determining whether the preceding row of the second updated row has formatting or formulas to be copied or applied to the second updated row and that either the row above the preceding row is blank, the row above the preceding row is empty, or the preceding row is the first row in the spreadsheet, and designating the preceding row to the second updated row as the identified set of peer rows in response to determining that the second updated row is at the bottom of a single peer row; and

in response to determining that the second 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 by determining whether the following row of the second updated row has formatting or formulas to be copied or applied to the second updated row and that either the row below the following row is blank, the row below the following row is empty, or the following row is the last row in the spreadsheet, and designating the following row to the second updated row as the identified set of peer rows in response to determining that the second updated row is at the top of a single peer row.

16. The computer-readable medium of claim 13, wherein modifying the updated row based on at least one of a formula or formatting from the identified set of peer rows of the updated row includes at least one of:

modifying formatting in the updated row based on formatting in a row of the identified set of peer rows, and

adding formulas to the updated row based on formulas in a row of the identified set of peer rows.

17. The computer-readable medium of claim 13, wherein modifying the updated row based on at least one of a formula or formatting from the identified set of peer rows of the updated row includes:

adding formulas to the updated row based on formulas in a row of the identified set of peer rows; and

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

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

detecting that a trigger has been activated, wherein the trigger indicates an updated row in a spreadsheet, and wherein the updated row is at a first indent level;

identifying a set of peer rows of the updated row, wherein identifying the set of peer rows of the updated row comprises;

determining at least two existing rows of the spreadsheet other than the updated row that are also at the first indent level and are separated from each other by at least one row at a different indent level;

comparing at least one of row-level formatting, cell-level formatting, and formulas of the at least two existing rows to each other; and

in response to determining that at least one of the row-level-formatting, cell-level formatting, and formulas of the at least two existing rows match, designating the at least two existing rows as an identified set of peer rows of the updated row; and

modifying the updated row based on at least one of a formula or formatting from the identified set of peer rows of the updated row.