Methods and systems for presenting drop-down, pop-up or other presentation of a multi-value data set in a spreadsheet cell
The technology disclosed relates to accessing data within or external to spreadsheet cells (NSC data) and presenting it to users, so they can select one or more value to populate the cell. In one implementation, the technology disclosed is a spreadsheet function (predefined formula) where the user inputs a cell range or NSC data formula resulting in a button operated drop-down (pop-up or other visual) populating one value within the cell. Once the formula is completed the user can change that value by clicking the button and selecting the desired value from the multiple values presented. In another implementation the function input can be altered to add a constraint (filter) limiting the values presented for selection. In variants of any of the implementations, the presented values can be organized in ascending or descending orders for easy selection and show all values or only the unique (distinct) values.
1 . A method of providing a user interface picklist function for inserting a value into a cell, using a spreadsheet predefined function running on a system including at least one processor and memory coupled to the processor including:
responsive to the user interface picklist function in the cell of the spreadsheet that references a list source that is not adjacent to the cell that holds the user interface picklist function,
the user interface picklist function automatically retrieving, sorting and deduplicating values from the list source without modifying the list source itself;
providing within the cell a manual trigger that the user selects to open the picklist;
making the picklist in the cell available responsive to the user selecting the manual trigger by presenting the sorted and deduplicated values; and
upon receiving a user selection from the picklist, of at least one value from the picklist, automatically making that at least one selected value available for use in the cell.
2 . The method of claim 1 , wherein at least one selection from the picklist is a value that has multiple instances and the multiple instances are returned by the picklist for use by one or more formulas that reference the cell.
3 . The method of claim 1 , wherein the list source is from Non-spreadsheet cell data.
4 . The method of claim 1 , wherein the list source is from spreadsheet cells.
5 . The method of claim 1 , wherein the list source is user inputted.
6 . The method of claim 1 , wherein syntax and arguments of the spreadsheet function are unchanged across different data sources.
7 . The method of claim 1 , wherein the source list is constrained (filtered) via a referenced cell by user specified value or values that are arguments of the picklist function.
8 . The method of claim 1 , wherein the picklist is presented from the cell in a drop-down or pop-up visual.
9 . The method of claim 1 , wherein the picklist after deduplicating is presented with a count of duplicate instances of the deduplicated values.
10 . The method of claim 1 , wherein the user can make more than one selection from the picklist and the user's multiple selections from the picklist will be stored in the cell.
11 . The method of claim 10 , wherein a cell equaling the picklist cell replicates the multiple values from the user's multiple selections, makes them usable by other cells but where the source list or picklist selected values can only be changed by changing the values in the cell.
12 . A method of applying a spreadsheet function, running on a system including at least one processor and memory coupled to the processor, that operates on values in a list or range of cells and further filters the values referenced by the list or range of cells, including:
the spreadsheet function evaluating the list or range on which the spreadsheet function operates and identifying a reference to a first cell;
returning from the first cell multiple values selected by a user from a user interface picklist function in the first cell, wherein the user interface picklist function displays a list of selectable values;
using the multiple values returned from the user interface picklist to filter values operated on by the spreadsheet function; and
the spreadsheet function calculating an algebraic or statistical result using the multiple values returned by the filter in the first cell.
13 . The method of claim 12 , wherein syntax of the spreadsheet function is unchanged by the method.
14 . The method of claim 12 , wherein the picklist function in the first cell composes the picklist from Non-spreadsheet cell data.
15 . The method of claim 12 , wherein the picklist function in the first cell composes the picklist from a range of cell data.
16 . The method of claim 12 , wherein the picklist function in the first cell composes the picklist from user input.
17 . The method of claim 12 , wherein syntax and arguments of the spreadsheet function are unchanged across different data sources.
18 . A computer-implemented system including at least one processor and memory coupled to the processor, the memory holding program instructions that, when executed, implement a method of providing a user interface picklist function for inserting a value into a cell, using a spreadsheet predefined function, the method including:
responsive to the user interface picklist function in the cell of the spreadsheet that references a list source that is not adjacent to the cell that holds the user interface picklist function,
the user interface picklist function automatically retrieving, sorting and deduplicating values from the list source without modifying the list source itself;
providing within the cell a manual trigger that the user selects to open the picklist;
making the picklist in the cell available responsive to the user selecting the manual trigger by presenting the sorted and deduplicated values; and
upon receiving a user selection from the picklist of at least one value from the picklist, automatically making that at least one selected value available for use in the cell.
19 . The system of claim 18 , wherein at least one selection from the picklist is a value that has multiple instances and the multiple instances are returned by the picklist for use by one or more formulas that reference the cell.
20 . The system of claim 18 , wherein the list source is from Non-spreadsheet cell data.
21 . The system of claim 18 , wherein the list source is from spreadsheet cells.
22 . The system of claim 18 , wherein the list source is user inputted.
23 . The system of claim 18 , wherein syntax and arguments of the spreadsheet function are unchanged across different data sources.
24 . The system of claim 18 , wherein the source list is constrained (filtered) via a referenced cell by user specified value or values that are arguments of the picklist function.
25 . The system of claim 18 , wherein the picklist is presented from the cell in a drop-down or pop-up visual.
26 . The system of claim 18 , wherein the picklist after deduplicating is presented with a count of duplicate instances of deduplicated values.
27 . The system of claim 18 , wherein the user can make more than one selection from the picklist and the user's multiple selections from the picklist will be stored in the cell.
28 . The system of claim 27 , wherein a cell equaling the picklist cell replicates the multiple values from the user's multiple selections, makes them usable by other cells but where the source list or picklist selected values can only be changed by changing the values in the cell.
29 . A system that applies a spreadsheet function, running on a system including at least one processor and memory coupled to the processor, that operates on values in a list or range of cells and further filters the values referenced by the list or range of cells, including:
a hardware processor, memory coupled to the processor, and instructions loaded into the memory configurable to cause the processor to carry at a method, including:
the spreadsheet function evaluating the list or range on which the spreadsheet function operates and identifying a reference to a first cell;
returning from the first cell multiple values selected by a user from a user interface picklist function in the first cell, wherein the user interface picklist function displays a list of selectable values;
using the multiple values returned from the user interface picklist to filter values operated on by the spreadsheet function; and
the spreadsheet function calculating an algebraic or statistical result using the multiple values returned by the filter in the first cell.
30 . The system of claim 29 , wherein syntax of the spreadsheet function is unchanged by the method.
31 . The system of claim 29 , wherein the picklist function in the first cell composes the picklist from Non-spreadsheet cell data.
32 . The system of claim 29 , wherein the picklist function in the first cell composes the picklist from a range of cell data.
33 . The system of claim 29 , wherein the picklist function in the first cell composes the picklist from user input.
34 . The system of claim 29 , wherein syntax and arguments of the spreadsheet function are unchanged across different data sources.
35 . A non-transitory computer-readable medium holding program instructions that, when executed on a processor, implement a method of providing a user interface picklist for inserting a value into a cell, using a spreadsheet (predefined formula) function, the method including:
responsive to the user interface picklist function in the cell of the spreadsheet that references a list source that is not adjacent to the cell that holds the user interface picklist function,
the user interface picklist function automatically retrieving, sorting and deduplicating values from the list source without modifying the list source itself;
providing within the cell a manual trigger that the user selects to open the picklist;
making the picklist in the cell available responsive to the user selecting the manual trigger by presenting the sorted and deduplicated values; and
upon receiving a user selection from the picklist of at least one value from the picklist, automatically making that at least one selected value available for use in the cell.
36 . The non-transitory computer-readable medium of claim 35 , wherein at least one selection from the picklist is a value that has multiple instances and the multiple instances are returned by the picklist for use by one or more formulas that reference the cell.
37 . The non-transitory computer-readable medium of claim 35 , wherein the list source is from Non-spreadsheet cell data.
38 . The non-transitory computer-readable medium of claim 35 , wherein the list source is from spreadsheet cells.
39 . The non-transitory computer-readable medium of claim 35 , wherein the list source is user inputted.
40 . The non-transitory computer-readable medium of claim 35 , wherein syntax and arguments of the spreadsheet function are unchanged across different data sources.
41 . The non-transitory computer-readable medium of claim 35 , wherein the source list is constrained (filtered) via a referenced cell by user specified value or values that are arguments of the picklist function.
42 . The non-transitory computer-readable medium of claim 35 , wherein the picklist is presented from the cell in a drop-down or pop-up visual.
43 . The non-transitory computer-readable medium of claim 35 , wherein the picklist after deduplicating is presented with a count of duplicate instances of the deduplicated values.
44 . The non-transitory computer-readable medium of claim 35 , wherein the user can make more than one selection from the picklist and the user's multiple selections from the picklist will be stored in the cell.
45 . The non-transitory computer-readable medium of claim 44 , wherein a cell equaling the picklist cell replicates the multiple values from the user's multiple selections, makes them usable by other cells but where the source list or picklist selected values can only be changed by changing the values in the cell.
46 . A non-transitory computer-readable medium including computer instructions that, when executed on a hardware processor, make the processor configurable to carry out a method of applying a spreadsheet function, running on a system including at least one processor and memory coupled to the processor, that operates on values in a list or range of cells and further filters the values referenced by the list or range of cells, the method including:
the spreadsheet function evaluating the list or range on which the spreadsheet function operates and identifying a reference to a first cell;
returning from the first cell multiple values selected by a user from a user interface picklist function in the first cell, wherein the user interface picklist function displays a list of selectable values;
using the multiple values returned from the user interface picklist to filter values operated on by the spreadsheet function; and
the spreadsheet function calculating an algebraic or statistical result using the multiple values returned by the filter in the first cell.
47 . The non-transitory computer-readable medium of claim 46 , wherein syntax of the spreadsheet function is unchanged by the method.
48 . The non-transitory computer-readable medium of claim 46 , wherein the picklist function in the first cell composes the picklist from Non-spreadsheet cell data.
49 . The non-transitory computer-readable medium of claim 46 , wherein the picklist function in the first cell composes the picklist from a range of cell data.
50 . The non-transitory computer-readable medium of claim 46 , wherein syntax and arguments of the spreadsheet function are unchanged across different data sources.
51 . The system of claim 29 , wherein the multiple values returned by the filter apply criteria constructed from the multiple values returned from the first cell, as if they were individual filter criteria combined by a logical OR using individual values returned by reference to multiple cells before the improvement.