Method and system for spreadsheet error identification and avoidance
Disclosed is a method of as you type error checking of a spreadsheet formula as a user types the formula in a cell, including analyzing, as you type, the cell contents of the spreadsheet formula and throwing an error message when the cell contents are not valid in the formula being typed. The error message identifies/emphasizes a token or group of tokens that cause the error. Also disclosed is selecting and populating into a spreadsheet formula a formulaic data field to be evaluated, starting from a position in the spreadsheet formula where the formulaic data field to be evaluated is to be populated, and responding to typed inputs and/or UI selection by displaying a list of selectable formulaic data fields with additional information that contains (non-data derived) human generated values. Upon selection among the fields, the method includes automatically populating into the starting position in the spreadsheet formula.
1 . A method for selecting and populating into a spreadsheet formula an argument in a formulaic data field to be evaluated, including:
starting from a position to be populated in an argument list of the formulaic data field in the spreadsheet formula to be evaluated, wherein:
the formulaic data field is a named data field within the a source of the formulaic data; and
formulaic data is sourced from either non-spreadsheet cells (NCS) or from spreadsheet cells, both sources being organized by named data fields;
responding to typed inputs and/or UI selection for the starting position to be populated by displaying a list of selectable options, the selectable options including direct and indirect filter options for the formulaic data field, wherein:
the direct filter options include values and retrievers that apply to the formulaic data field itself; and
the indirect filter options include constraints on fields from a same table as the formulaic data field; and
upon selection among the selectable options, automatically populating a selected direct or indirect filter applicable to formulaic data field into the starting position.
2 . The method of claim 1 , further including responding to the typed inputs and/or UI selection with additional information for the list of selectable options, including informative descriptions of data field content of the formulaic data fields in the list, and informative and descriptive information beyond data field names.
3 . The method of claim 1 , further including displaying the direct and indirect filter options in separate selection list buckets.
4 . The method of claim 1 , wherein the direct filter list of selectable options displayed is context sensitive to whether the formulaic data field in the spreadsheet formula can populate into the starting position multiple values, as opposed to a single value.
5 . The method of claim 1 , wherein direct filter list of selectable options displayed is context sensitive to a prebuilt spreadsheet function being applied to the formulaic data field in the spreadsheet formula being evaluated.
6 . The method of claim 1 , wherein the direct and/or indirect filter list of selectable options displayed are context sensitive to and limited by prior argument values of the formulaic data field in the spreadsheet formula being evaluated.
7 . The method of claim 1 , wherein, in the indirect filter list of selectable options, indirect filter option(s) already in use for the formulaic data field are visually differentiated from the unused indirect filter options.
8 . The method of claim 1 , wherein, in the indirect filter list of selectable options, one or more indirect filter option(s) already in use for the formulaic data field are disabled from selection in the indirect filter options.
9 . The method of claim 1 , wherein the direct and indirect filter lists of selectable options displayed are context sensitive to the data type of the formulaic data field.
10 . The method of claim 1 , wherein the direct and/or indirect filter list of selectable options displayed are context sensitive to values present in the formulaic data field.
11 . The method of claim 2 , wherein additional information in the direct and/or indirect filter list of selectable options displayed is context sensitive to the data type of the formulaic data field.
12 . The method of claim 1 , wherein the selection of the option automatically populates a text or date data type into a direct filter selection with a proper text or date syntax.
13 . The method of claim 1 , wherein the indirect filter options further include fields from a different table than the formulaic data field.
14 . The method of claim 1 , wherein selection of an indirect formulaic data field for the indirect filter causes display a context-sensitive list of selectable indirect filter options.
15 . The method of claim 1 , wherein selection of a second indirect filter to replace a first indirect filter automatically eliminates direct filter options specified for the first indirect filter.
16 . The method of claim 1 , further including causing display a hint for the selection, wherein the hint includes non-selectable instructional text, selection options, and non-selectable headers.
17 . A non-transitory computer readable medium impressed with instructions that, when executed on a processor, implement actions for selecting and populating into a spreadsheet formula an argument in a formulaic data field to be evaluated, including:
starting from a position to be populated in an argument list of the formulaic data field in the spreadsheet formula to be evaluated, wherein:
the formulaic data field is a named data field within a source of the formulaic data; and
formulaic data is sourced from either non-spreadsheet cells (NCS) or from spreadsheet cells, both sources being organized by named data fields;
responding to typed inputs and/or UI selection for the starting position to be populated by displaying a list of selectable options, the selectable options including direct and indirect filter options for the formulaic data field, wherein:
the direct filter options include values and retrievers that apply to the formulaic data field itself; and
the indirect filter options include constraints on fields from a same table as the formulaic data field; and
upon selection among the selectable options, automatically populating a selected direct or indirect filter applicable to formulaic data field into the starting position.
18 . The non-transitory computer readable medium of claim 17 , wherein, in the indirect filter list of selectable options, indirect filter option(s) already in use for the formulaic data field are visually differentiated from the unused indirect filter options.
19 . The non-transitory computer readable medium of claim 17 , wherein the direct and/or indirect filter list of selectable options displayed are context sensitive to values present in the formulaic data field.
20 . The non-transitory computer readable medium of claim 17 , wherein the argument list begins and ends with curly brackets.
21 . The non-transitory computer readable medium of claim 17 , wherein the selection of the option automatically populates a text or date data type into a direct filter selection with a proper text or date syntax.
22 . A computer-implemented system including at least one processor and memory coupled to the processor, the memory holding program instructions that implements a method for selecting and populating into a spreadsheet formula an argument in a formulaic data field to be evaluated, including:
starting from a position to be populated in an argument list of the formulaic data field in the spreadsheet formula to be evaluated, wherein:
the formulaic data field is a named data field within a source of the formulaic data; and
formulaic data is sourced from either non-spreadsheet cells (NCS) or from spreadsheet cells, both sources being organized by named data fields;
responding to typed inputs and/or UI selection for the starting position to be populated by displaying a list of selectable options, the selectable options including direct and indirect filter options for the formulaic data field, wherein:
the direct filter options include values and retrievers that apply to the formulaic data field itself; and
the indirect filter options include constraints on fields from a same table as the formulaic data field; and
upon selection among the selectable options, automatically populating a selected direct or indirect filter applicable to formulaic data field into the starting position.
23 . A computer system of claim 22 , wherein the direct filter list of selectable options displayed is context sensitive to whether the formulaic data field in the spreadsheet formula can populate into the starting position multiple values, as opposed to a single value.
24 . A computer system of claim 22 , wherein direct filter list of selectable options displayed is context sensitive to a prebuilt spreadsheet function being applied to the formulaic data field in the spreadsheet formula being evaluated.
25 . A computer system of claim 22 , wherein the direct and/or indirect filter list of selectable options displayed are context sensitive to and limited by prior argument values of the formulaic data field in the spreadsheet formula being evaluated.