Method and apparatus for using label references in spreadsheet formulas

• US 5,987,481 A
• Filed: 07/01/1997
• Issued: 11/16/1999
• Est. Priority Date: 07/01/1997
• Status: Expired due to Term

• Current Assignee: Microsoft Technology Licensing LLC (Microsoft Corporation)
• Original Assignee: Microsoft Corporation
• Inventors: Lange, Jonathan E., Barnett, Joseph David, Michelman, Eric H.
• Primary Examiner(s): Lintz, Paul R.
• Assistant Examiner(s): Alam, Shahid
• Application Number:
  US08/886,759
• Time in Patent Office: 868 Days
• Field of Search: 707/500, 707/503, 707/507, 707/501, 345/333, 345/335
• US Class Current: 715/219
• CPC Class Codes: G06F 40/18 of spreadsheets form-filling G06F40/174

Abstract

A system for referencing a range of cells in a spreadsheet formula by using label references. The label references are used to identify labels within the spreadsheet which, in turn, refer to a range of cells. First, a formula is parsed in order to identify any label references within the formula. Second, the cells of the spreadsheet are searched in order to generate a list of labels within the spreadsheet that match the label references identified in the formula. Third, each of the labels are examined and a preferred label set is selected. The preferred label set represents the actual labels that were intended by the formula references. Finally, the range of cells referenced by the preferred label set is determined. Thus, a user is able to reference a cell within a spreadsheet table by entering column and row labels that intersect at that cell.

Claims

1. In a spreadsheet having a plurality of cells arranged in a grid of rows and columns, said cells being receptive of data-items, labels and formulas, a method for referencing a range of cells in a spreadsheet formula by using label references, said label references identifying a preferred set of labels within the spreadsheet which correspond to said range of cells, said method comprising the steps of:

parsing said formula for a group of label references;

for each said label reference in said group, searching said cells of said spreadsheet to generate a target label list;

selecting said preferred label set from said target label lists; and

determining said range of cells referenced by said preferred label set.

2. The method of claim 1, wherein said parsing step further comprises the steps of:

identifying two delimiters in said formula separated by at least one token;

examining each said token and each combination of at least two adjacent tokens to identify proper label references; and

for each proper label reference, including said token or said combination of two or more adjacent tokens in said group of label references.

3. The method of claim 2, wherein said examining step further comprises the step of identifying a token comprising a set of characters enclosed by label boundary marks as a proper label reference.

4. The method of claim 2, wherein said examining step further comprises the step of identifying a token comprising an integer in the range of 1900 to 9999 as a proper label reference.

5. The method of claim 2, wherein said examining step further comprises the step of identifying a token comprising a set of characters formatted as a date as a proper label reference.

6. The method of claim 1, wherein said spreadsheet contains at least one labeled range and said searching step further comprises the step of searching said labeled ranges for target labels which correspond with said label reference.

7. The method of claim 1, wherein said spreadsheet comprises a plurality of plies, each of said plies having a ply-name, said label reference beginning with a ply-name of a particular ply, and said searching step further comprises the step of searching the cells of said particular ply for target labels corresponding with the portion of said label reference which does not contain said ply-name.

8. The method of claim 1, wherein said selecting step further comprises the steps of:

identifying candidate matching label sets from said target label lists, each said candidate matching label set containing at least one target label;

eliminating non-preferred candidate matching label sets until a final candidate matching label set remains; and

selecting said final candidate matching label set as said preferred label set.

9. The method of claim 8, wherein an implicit intersection match and a free radical intersection match comprise a single target label from one of said target label lists, an explicit intersection match comprises a first target label from a first target label list and a second target label from a second target label list, and said step of identifying candidate matching label sets further comprises the steps of:

for each said target label list, identifying candidate implicit intersection matches;

for each said target label list, identifying candidate free radical intersection matches; and

for each combination of two target label lists, identifying candidate explicit intersection matches.

10. The method of claim 8, wherein said step of eliminating non-preferred candidate matching label sets further comprises the step of classifying each said target label in each said candidate matching label set as one of the types including a row header, a column header and indeterminate.

11. The method of claim 10, wherein said classifying step further comprises the steps of:

identifying a data-type of said target label;

determining the number of cells positioned horizontally about said target label which are in a range of N cells from said target label and which contain said data-type, where N is an integer;

determining the number of cells positioned vertically about said target label which are in said range of N cells and which contain said data-type;

classifying said target label as a column header if said number of cells positioned horizontally about said target label is X greater than said number of cells positioned vertically about said target label, where X is an integer less than 2*N; and

classifying said target label as a row header if said number of cells positioned vertically about said target label is X greater than the number of cells positioned horizontally about said target label.

12. The method of claim 11, wherein if said target label is not classified as a column header or a row header, said classifying said target label step further comprises the steps of:

classifying said target label as a column header if one of the following two conditions exist, the conditions including N cells above said target label and N cells to the left of said target label being blank, and N cells above said target label and N cells to the right of said target label being blank;

classifying said target label as a row header if one of the following two conditions exist, the conditions including N cells below said target label and N cells to the left of said target label being blank, and N cells below said target label and N cells to the right of said target label being blank;

if said target label has not been, classifying said target label as indeterminate.

13. The method of claim 10, wherein for each candidate explicit intersection match, said step of eliminating non-preferred candidate matching label sets further comprises the steps of:

if said first target label and said second target label are both classified as row headers or if said first target label and said second target label are both classified as column headers, eliminating said candidate explicit intersection match; and

if said first target label and said second target label are located in the same row or if said first target label and said second target label are located in the same column, eliminating said candidate explicit intersection match.

14. The method of claim 10, wherein for each candidate explicit intersection match which qualifies as a Single-Double match by having said first target label list containing multiple instances of said first target label and said second target label list containing one instance of said second target label, said step of eliminating non-preferred candidate matching label sets further comprises the steps of:

if said candidate explicit intersection match is located within the boundaries of an implicit table which includes said formula, eliminating candidate explicit intersection matches that are not located within the boundaries of said implicit table;

if said second target label is a member of a labeled range and at least one instance of said first target label is a member of said labeled range, eliminating candidate explicit intersection matches where said first target label is not a member of said labeled range;

if said second target label is classified as a column header and at least one instance of said first target label is located to the left of said second target label, eliminating candidate matches where said first target label is not located to the left of said second target label;

if said second target label is classified as a row header and at least one instance of said first target label is located to the right of said second target label, eliminating candidate matches where said first target label is not located to the right of said second target label; and

if at least one instance of said first target label can be identified as a closest first target label, said closest first target label being closer to said second target label than other instances of said first target label, eliminating candidate explicit intersection matches which do not comprise one of said closest first target labels.

15. The method of claim 10, wherein for each candidate explicit intersection match which qualifies as a Double-Double match by having said first target label list containing multiple instances of said first target label and said second target label list containing multiple instances of said second target label, and said step of eliminating non-preferred candidate matching label sets further comprises the steps of:

if said candidate explicit intersection match is located within the boundaries of an implicit table which includes said formula, eliminating candidate explicit intersection matches that are not located within the boundaries of said implicit table;

if said first target label is classified as a column header and said second target label is classified as a row header, and said first target label is positioned to the right of said second target label, eliminating candidate explicit intersection matches which do not meet this criteria;

if at least one instance of said first target label can be identified as a closest first target label, said closest first target label being closer to said second target label than other instances of said first target label, eliminating candidate explicit intersection matches which do not comprise one of said closest first target labels;

if at least one instance of said first target label is classified as a row header and can be identified as a closest row header, said closest row header being closer to said formula than other instances of said first target label, eliminating candidate explicit intersection matches which do not comprise one of said closest row headers;

if at least one instance of said first target label is classified as a column header and can be identified as a closest column header, said closest column header being closer to said formula than other instances of said first target label, eliminating candidate explicit intersection matches which do not comprise one of said closest column headers; and

if at least one instance of said first target label is classified as a column header and is located above said formula, and at least one instance of said second target label is classified as a row header and is located to the left of said formula, eliminating candidate explicit intersection matches which are not comprised of these labels.

16. The method of claim 10, wherein for each candidate implicit intersection match and for each candidate free radical intersection match having multiple instances of said single target label in said target label list, said step of eliminating non-preferred candidate matching label sets further comprises the steps of:

if said single target label is classified as a column header and is positioned below said formula, eliminating said candidate implicit intersection match;

if at least one instance of said single target label is positioned within an implicit table which includes said formula, eliminating candidate implicit intersection matches comprising instances of said single target label which are not positioned within said implicit table;

if at least one instance of said single target label is classified as a row header and is positioned to the left of said formula, eliminating candidate implicit intersection matches comprising instances of said single target label which are row headers and are not positioned to the left of said formula;

if at least one instances of said single target label is positioned within the current region for said formula, said current region being the group of cells including the formula cell that are bounded by the smallest rectangle of blank cells, eliminating candidate implicit intersection matches comprising instances of said single target label not positioned within said current region; and

if all instances of said single target label are positioned in the same quadrant about the formula, and if at least one instance of said single target label can be identified as a closest single target label, said closest single target label being closer to said formula than other instances of said single target label, eliminating said candidate implicit intersection matches which do not comprise one of said closest single target labels.

17. The method of claim 1, wherein said preferred label set contains a first label in a first row of said spreadsheet and a second label in a second row of said spreadsheet, and said determining step further comprises the steps of:

if said first label is a row header and said second label is a column header, equating said range of cells to the cell intersected by the row containing said first label and the column containing said second label and which is positioned below said second label;

if said first label is a row header and said second label is indeterminate, equating said range of cells to the cell intersected by the row containing said first label and the column containing said second label and which is positioned below said second label;

if said first label is a column header and said second label is indeterminate, equating said range of cells to the cell intersected by the column containing said first label and the row containing said second label and which is positioned below said first label; and

if said first label and said second label are indeterminate and said first label is positioned above said second label, equating said range of cells to the cell intersected by the column containing said first label and the row containing said second label and which is positioned below said first label.

18. The method of claim 1, wherein said preferred label set contains one label, said label being positioned in a different row and column than said formula, and said determining step further comprises the steps of:

if said formula is located above said label, equating said range of cells to the cell intersected by the row containing said label and the column containing said formula and which is positioned below said formula;

if said formula is located below and to the left of said label, equating said range of cells to the cell intersected by the column containing said label and the row containing said formula and which is positioned below said label;

if said formula is located below and to the right of said label, then perform the steps of;

classifying said label as one of the types including a row header, a column header and indeterminate types, based on the content of the cells surrounding said label,

if said label is classified as a row header, equating said range of cells to the cell intersected by the row containing said label and the column containing said formula and which is positioned above said formula;

if said label is classified as a column header, equating said range of cells to the cell intersected by the column containing said label and the row containing said formula and which is positioned below said label;

if said label is classified as indeterminate and if the entire current region for said label is located above but not to the left of said formula, equating said range of cells to the cell intersected by the row containing said label and the column containing said formula and which is positioned above said formula; and

in all other cases, equating said range of cells to the cell intersected by the column containing said label and the row containing said formula and which is positioned below said label.

19. The method of claim 1, wherein said preferred label set contains one label sharing one of two common alignment features with said formula including common row and common column, and said determining step further comprises the steps of:

if a first current region for said label includes said formula, said first current region being the group of cells containing said label and being bounded by the smallest rectangle of blank cells, remove said formula from said first current region;

if said label is a column header, and if said label is on the bottom row of said first current region, equating said range of cells to all cells positioned below said label and within a second current region, said second current region being the smallest area of cells containing the next occupied cell positioned below said label and being surrounded by blank cells;

if said label is a column header, and if said label is not on the bottom row of said first current region, identifying said range of cells as comprising all cells positioned below said label and within said first current region;

if said label type is a row header, equating said range of cells to all cells positioned to the right of said label, within the same row as said label and within said first current region, unless an exception occurs including;

said label being positioned in the right most column of said first current region,

said formula being positioned immediately to the right of said label, and

the cell immediately to the right of said label containing a blank, said first current region being confined to the column containing said label and the column containing said blank, and a second current region for the cell immediately to the left of said label extending left from said label.

20. In a program module responsive to input commands for entering, modifying and deleting information items in a plurality of information item place-holders, said information items comprising data items, labels and formulas, a method for referencing a range of said place-holders within a formula by using at least one reference to a preferred label set, comprising the steps of:

parsing said formula for a group of said references to said labels;

for each said reference in said group, searching said place- holders for matching labels to generate a target label list;

selecting said preferred label set from said target label lists; and

determining said range of said place-holders referenced by said preferred label set.

21. The method of claim 20, wherein said parsing step further comprises the steps of:

identifying two delimiters in said formula separated by at least one token;

examining each said token and each combination of at least two adjacent tokens to identify proper references to said labels; and

for each proper reference to said label, including said token and said combination of at least two adjacent tokens in said group.

22. The method of claim 21, wherein said examining step further comprises the step of identifying said token or said combination of two or more adjacent tokens as a proper reference to said label if said token or said combination contains at least one of the following characteristics including:

a set of characters enclosed by label boundary marks;

an integer in the range of 1900 to 9999; and

a set of characters formatted as a date.

23. The method of claim 20, wherein said selecting step further comprises the steps of:

identifying candidate matching label sets from said target label lists, each said candidate matching label set containing at least one target label;

eliminating non-preferred candidate matching label sets until a final candidate matching label set remains; and

selecting said final candidate matching label set as said preferred label set.

24. The method of claim 23, wherein an implicit intersection match and a free radical intersection match comprise a single target label from one of said target label lists, an explicit intersection match comprises a first target label from a first target label list and a second target label from a second target label list, and said step of identifying candidate matching label sets further comprises the steps of:

for each said target label list, identifying candidate implicit intersection matches;

for each said target label list, identifying candidate free radical intersection matches; and

for each combination of two target label lists, identifying candidate explicit intersection matches.

25. The method of claim 24, wherein said place-holders are arranged in a grid of rows and columns and said step of eliminating non-preferred candidate matching label sets further comprises the step of classifying each said target label in each said candidate matching label set as one of the types including a row header, a column header and indeterminate type.

26. The method of claim 25, wherein for each candidate explicit intersection match, said step of eliminating non-preferred candidate matching label sets further comprises the steps of:

eliminating said candidate explicit intersection match if said first target label and said second target label are both classified as row headers;

eliminating said candidate explicit intersection match if said first target label and said second target label are both classified as column headers;

eliminating said candidate explicit intersection match if said first target label and said second target label are located in the same row; and

eliminating said candidate explicit intersection match if said first target label and said second target label are located in the same column.

27. The method of claim 25, wherein for each candidate explicit intersection match which qualifies as a Single-Double match by having said first target label list containing multiple instances of said first target label and said second target label list containing one instance of said second target label, said step of eliminating non-preferred candidate matching label sets further comprises the steps of:

if said candidate explicit intersection match is located within the boundaries of an implicit table which includes said formula, eliminating candidate explicit matches that are not located within the boundaries of said implicit table;

if said second target label is a member of a labeled range and at least one instance of said first target label is a member of said labeled range, eliminating candidate explicit intersection matches where said first target label is not a member of said labeled range;

if said second target label is classified as a column header and at least one instance of said first target label is located to the left of said second target label, eliminating candidate matches where said first target label is not located to the left of said second target label;

if said second target label is classified as a row header and at least one instance of said first target label is located to the right of said second target label, eliminating candidate matches where said first target label is not located to the right of said second target label; and

if at least on instance of said first target label can be identified as a closest first target label, said closest first target label being closer to said second target label than other instances of said first target label, eliminating candidate explicit intersection matches which do not comprise one of said closest first target labels.

28. The method of claim 25, wherein for each candidate explicit intersection match which qualifies as a Double-Double match by having said first target label list containing multiple instances of said first target label and said second target label list containing multiple instances of said second target label, said step of eliminating non-preferred candidate matching label sets further comprises the steps of:

if said candidate explicit intersection match is located within the boundaries of an implicit table which includes said formula, eliminating candidate explicit matches that are not located within the boundaries of said implicit table;

if said first target label is classified as a column header and said second target label is classified as a row header, and said first target label is positioned to the right of said second target label, eliminating candidate explicit intersection matches which do not meet this criteria;

if at least one instance of said first target label can be identified as a closest first target label, said closest first target label being closer to said second target label than other instances of said first target label, eliminating candidate explicit intersection matches which do not comprise one of said closest first target labels;

if at least one instance of said first target label is classified as a row header and can be identified as a closest row header, said closest row header being closer to said formula than other instances of said first target label, eliminating candidate explicit intersection matches which do not comprise one of said closest row headers;

if at least one instance of said first target label is classified as a column header and can be identified as a closest column header, said closest column header being closer to said formula than other instances of said first target label, eliminating candidate explicit intersection matches which do not comprise one of said closest column headers; and

if at least one instance of said first target label is classified as a column header and is located above said formula, and at least one instance of said second target label is classified as a row header and is located to the left of said formula, eliminating candidate explicit intersection matches which are not comprised of these labels.

29. The method of claim 25, wherein for each candidate implicit intersection match and each candidate free radical intersection match having multiple instances of said single target label in said target label list, said step of eliminating non-preferred candidate matching label sets further comprises the steps of:

if said single target label is classified as a column header and is positioned below said formula, eliminating said candidate implicit intersection match;

if at least one instance of said single target label is positioned within an implicit table which includes said formula, eliminating candidate implicit intersection matches comprising instances of said single target label which are not positioned within said implicit table;

if at least one instance of said single target label is classified as a row header and is positioned to the left of said formula, eliminating candidate implicit intersection matches comprising instances of said single target label which are row headers and are not positioned to the left of said formula;

if at least one instances of said single target label is positioned within the current region for said formula, said current region being the smallest set of place-holders containing said formula and which are surrounded by a rectangle of blank cells, eliminating candidate implicit intersection matches comprising instances of said single target label not positioned within said current region; and

if all instances of said single target label are positioned in the same quadrant about the formula, and if at least one instance of said single target label can be identified as a closest single target label, said closest single target label being closer to said formula than other instances of said single target label, eliminating said candidate implicit intersection matches which do not comprise one of said closest single target labels.

30. The method of claim 20, wherein said place-holders are arranged in a grid of rows and columns, said preferred label set contains a first label in a first row of said spreadsheet and a second label in a second row of said spreadsheet, said first label and said second label being classified one of the types including a row header, column header or indeterminate type, and said determining step further comprises the steps of:

if said first label is classified as a row header and said second label is classified as a column header, equating said range of place-holders to the place-holder intersected by the row containing said first label and the column containing said second label and which is positioned below said second label;

if said first label is classified as a row header and said second label is classified as indeterminate, equating said range of place-holders to the place-holder intersected by the row containing said first label and the column containing said second label and which is positioned below said second label;

if said first label is classified as a column header and said second label is classified as indeterminate, equating said range of place-holders to the place-holder intersected by the column containing said first label and the row containing said second label and which is positioned below said first label; and

if said first label and said second label are classified as indeterminate and said first label is positionally above said second label, equating said range of place-holders to the place-holder intersected by the column containing said first label and the row containing said second label and which is positioned below said first label.

31. The method of claim 20, wherein said place-holders are arranged in a grid of rows and columns, said preferred label set contains one label, and said determining step further comprises the steps of:

if said formula is located above said label, equating said range of place-holders to the place-holder intersected by the row containing said label and the column containing said formula and which is positioned below said formula;

if said formula is located below and to the left of said label, equating said range of cells to the cell intersected by the column containing said label and the row containing said formula and which is positioned below said label;

if said formula is located below and to the right of said label, then perform the steps of;

classifying said label as one of the types including a row header, a column header and indeterminate type based on the content of the cells surrounding said label,

if said label is classified as a row header, equating said range of place-holders to the place-holder intersected by the row containing said label and the column containing said formula and which is positioned above said formula;

if said label is classified as a column header, equating said range of place-holders to the place-holder intersected by the column containing said label and the row containing said formula and which is positioned below said label;

if said label is classified as indeterminate and if the entire current region for said label is located above but not to the left of said formula, equating said range of place-holders to the place-holder intersected by the row containing said label and the column containing said formula and which is positioned above said formula; and

in all other cases, equating said range of place-holders to the place-holder intersected by the column containing said label and the row containing said formula and which is positioned below said label.

32. The method of claim 20, wherein said place-holders are arranged in a grid of rows and columns, said preferred label set contains one label sharing a common alignment feature with said formula including one of a common row and a common column, and said determining step further comprises the steps of:

if a first current region for said label includes said formula, said first current region being the smallest set of place-holders containing said label and being surrounded by a rectangle of blank cells, remove said formula from said first current region;

if said label is a column header, and if said label is on the bottom row of said first current region, equating said range of place-holders to all place-holders positioned below said label and within a second current region, said second current region being the smallest set of place-holders containing the next occupied place-holder positioned below said label and being surrounded by blank cells;

if said label is a column header, and if said label is not on the bottom row of said first current region, identifying said range of place-holders as comprising all place-holders positioned below said label and within said first current region;

if said label type is a row header, equating said range of place-holders to all place-holders positioned to the right of said label, within the same row as said label and within said first current region, unless at least one exception occurs of the possible exceptions including;

said label being positioned in the right most column of said first current region,

said formula being positioned immediately to the right of said label, and

the place-holder immediately to the right of said label containing a blank, said first current region being confined to the column containing said label and the column containing said blank, and a second current region for the place-holder immediately to the left of said label extending left from said label.

33. A computer-readable medium on which is stored a computer program for using label references in a formula to reference a range of cells in a spreadsheet, said spreadsheet having a plurality of cells arranged in a grid of rows and columns, said cells receptive of data items, formulas and labels, said computer program comprising instructions which, when executed by said computer, perform the steps of:

parsing said formula for a group of label references;

for each said label reference in said group, searching said cells of said spreadsheet to generate a target label list;

selecting a preferred label set from said target label lists; and

determining said range of cells referenced by said preferred label set.

34. The method of claim 33, wherein said parsing step further comprises the steps of:

identifying two delimiters in said formula separated by at least one token;

examining each said token and each combination of at least two adjacent tokens to identify proper label references; and

for each proper label reference, including said token and said combination of at least two tokens in said group of label references.

35. The method of claim 34, wherein said examining step further comprises the step of identifying as a proper label reference, each said token and each said combination of at least two adjacent tokens that comprise at least one of the characteristics including:

a set of characters enclosed by label boundary;

an integer in the range of 1900 to 9999; and

a set of characters formatted as a date.

36. The method of claim 33, wherein said selecting step further comprises the steps of:

identifying candidate matching label sets from said target label lists, each said candidate matching label set containing at least one target label;

eliminating non-preferred candidate matching label sets until a final candidate matching label set remains; and

selecting said final candidate matching label set as said preferred label set.

37. The method of claim 36, wherein an implicit intersection match and a free radical intersection match comprise a single target label from one of said target label lists, an explicit intersection match comprises a first target label from a first target label list and a second target label from a second target label list, and said step of identifying candidate matching label sets further comprises the steps of:

for each said target label list, identifying candidate implicit intersection matches;

for each said target label list, identifying candidate free radical intersection matches; and

for each combination of two target label lists, identifying candidate explicit intersection matches.

38. The method of claim 36, wherein said step of eliminating non-preferred candidate matching label sets further comprises the step of classifying each target label in each said candidate matching label set as one of the types including a row header, a column header and indeterminate type.

39. The method of claim 38, wherein for each candidate explicit intersection match, said step of eliminating non-preferred candidate matching label sets further comprises the steps of:

eliminating said candidate explicit intersection match if said first target label and said second target label are both classified as row headers

eliminating said candidate explicit intersection match if said first target label and said second target label are both classified as column headers; and

eliminating said candidate explicit intersection match if said first target label and said second target label are located in the same row; and

eliminating said candidate explicit intersection match if said first target label and said second target label are located in the same column.

40. The method of claim 38, wherein for each candidate explicit intersection match which qualifies as a Single-Double match by having said first target label list containing multiple instances of said first target label and said second target label list containing one instance of said second target label, said step of eliminating non-preferred candidate matching label sets further comprises the steps of:

if said candidate explicit intersection match is located within the boundaries of an implicit table which includes said formula, eliminating candidate explicit matches that are not located within the boundaries of said implicit table;

if said second target label is a member of a labeled range and at least one instance of said first target label is a member of said labeled range, eliminating candidate explicit intersection matches where said first target label is not a member of said labeled range;

if said second target label is classified as a column header and at least one instance of said first target label is located to the left of said second target label, eliminating candidate matches where said first target label is not located to the left of said second target label;

if said second target label is classified as a row header and at least one instance of said first target label is located to the right of said second target label, eliminating candidate matches where said first target label is not located to the right of said second target label; and

if at least one instance of said first target label can be identified as a closest first target label, said closest first target label being closer to said second target label than other instances of said first target label, eliminating candidate explicit intersection matches which do not comprise one of said closest first target labels.

41. The method of claim 38, wherein for each candidate explicit intersection match which qualifies as a Double-Double match by having said first target label list containing multiple instances of said first target label and said second target label list containing multiple instances of said second target label, said step of eliminating non-preferred candidate matching label sets further comprises the steps of:

if said candidate explicit intersection match is located within the boundaries of an implicit table which includes said formula, eliminating candidate explicit intersection matches that are not located within the boundaries of said implicit table;

if said first target label is classified as a column header and said second target label is classified as a row header, and said first target label is positioned to the right of said second target label, eliminating candidate explicit intersection matches which do not meet this criteria;

if at least one instance of said first target label are classified as a row header and can be identified as a closest row header, said closest row header being closer to said formula than other instances of said first target label, eliminating candidate explicit intersection matches which do not comprise one of said closest row headers; and

if at least one instance of said first target label are classified as a column header and can be identified as a closest column header, said closest column header being closer to said formula than other instances of said first target label, eliminating candidate explicit intersection matches which do not comprise one of said closest column headers.

42. The method of claim 38, wherein for each candidate implicit intersection match and each candidate free radical intersection match having multiple instances of said single target label in said target label list, said step of eliminating non-preferred candidate matching label sets further comprises the steps of:

if said single target label is classified as a column header and is positioned below said formula, eliminating said candidate implicit intersection match;

if at least one instance of said single target label is positioned within an implicit table which includes said formula, eliminating candidate implicit intersection matches comprising instances of said single target label which are not positioned within said implicit table;

if at least one instance of said single target label is classified as a row header and is positioned to the left of said formula, eliminating candidate implicit intersection matches comprising instances of said single target label which are row headers and are not positioned to the left of said formula;

if at least one instances of said single target label is positioned within the current region for said formula, said current region being the smallest area of cells containing said formula and being surrounded by a rectangle of blank cells, eliminating candidate implicit intersection matches comprising instances of said single target label not positioned within said current region; and

if all instances of said single target label are positioned in the same quadrant about the formula, and if at least one instance of said single target label can be identified as a closest single target label, said closest single target label being closer to said formula than other instances of said single target label, eliminating candidate implicit intersection matches which do not comprise one of said closest single target labels.

43. The method of claim 33, wherein said preferred label set contains a first label in a first row of said spreadsheet and a second label in a second row of said spreadsheet, said first label and said second label are classified as one of the types including a row header, a column header or indeterminate type, and said determining step further comprises the steps of:

if said first label is a row header and said second label is a column header, equating said range of cells to the cell intersected by the row containing said first label and the column containing said second label and which is positioned below said second label;

if said first label is a row header and said second label is indeterminate, equating said range of cells to the cell intersected by the row containing said first label and the column containing said second label and which is positioned below said second label;

if said first label is a column header and said second label is indeterminate, equating said range of cells to the cell intersected by the column containing said first label and the row containing said second label and which is positioned below said first label; and

if said first label and said second label are indeterminate and said first label is positioned above said second label, equating said range of cells to the cell intersected by the column containing said first label and the row containing said second label and which is positioned below said first label.

44. The method of claim 33, wherein said preferred label set contains one label, said label being positioned in a different row and column than said formula, said determining step further comprises the steps of:

if said formula is located above said label, equating said range of cells to the cell intersected by the row containing said label and the column containing said formula and which is positioned below said formula;

if said formula is located below and to the left of said label, equating said range of cells to the cell intersected by the column containing said label and the row containing said formula and which is positioned below said label;

if said formula is located below and to the right of said label, then perform the steps of;

classifying said label as one of the types including a row header, a column header and indeterminate type based on the content of the cells surrounding said label,

if said label is classified as a row header, equating said range of cells to the cell intersected by the row containing said label and the column containing said formula and which is positioned above said formula;

if said label is classified as a column header, equating said range of cells to the cell intersected by the column containing said label and the row containing said formula and which is positioned below said label;

if said label is classified as indeterminate and if the entire current region for said label is located above but not to the left of said formula, equating said range of cells to the cell intersected by the row containing said label and the column containing said formula and which is positioned above said formula; and

in all other cases, equating said range of cells to the cell intersected by the column containing said label and the row containing said formula and which is positioned below said label.

45. The method of claim 33, wherein said preferred label set contains one label sharing a common alignment feature with said formula including one of a common row and a common column, said label is classified as one of the types including a row header, a column header or indeterminate type, and said determining step further comprises the steps of:

if a first current region for said label includes said formula, said first current region being the smallest area of cells containing said label and being surrounded by blank cells, remove said formula from said first current region;

if said label is a column header, and if said label is on the bottom row of said first current region, equating said range of cells to all cells positioned below said label and within a second current region, said second current region being the smallest area of cells containing the next occupied cell positioned below said label and being surrounded by blank cells;

if said label is a column header, and if said label is not on the bottom row of said first current region, identifying said range of cells as comprising all cells positioned below said label and within said first current region;

if said label type is a row header, equating said range of cells to all cells positioned to the right of said label, within the same row as said label and within said first current region, unless at least one exception applies including;

said label being positioned in the right most column of said first current region,

said formula being positioned immediately to the right of said label, and

the cell immediately to the right of said label containing a blank, said first current region being confined to the column containing said label and the column containing said blank, and a second current region for the cell immediately to the left of said label extending left from said label.

46. A computer system for using label references in a formula to reference a range of cells in a spreadsheet, comprising:

a processing unit;

a memory storage device;

an input device coupled to said processing unit for receiving data;

a pixel-based display device coupled to said processing unit for displaying data;

a program module, stored in said memory storage device for providing instructions to said processing unit;

said processing unit, responsive to said instructions of said program module, operative to;

parse a formula entered from said input device for a group of label references by;

identifying two delimiters in said formula separated by at least one token,

examining each said token and each combination of at least two adjacent tokens to identify proper label references, a proper label reference including a set of characters enclosed by label boundary marks, an integer in the range of 1900 to 9999, and a set of characters formatted as a date, and

for each proper label reference, including each said token and each said combination of at least two adjacent tokens in said group of label references;

search said cells of said spreadsheet to generate a target label list for each said label reference in said group;

select a preferred label set from said target label lists by;

identifying candidate matching label sets from said target label lists, each said candidate matching label set containing at least one target label,

eliminating non-preferred candidate matching label sets until a final candidate matching label set remains, and

setting said final candidate matching label set as said preferred label set; and

determining said range of cells referenced by said preferred label set.

47. The computer system of claim 46, wherein an implicit intersection match and a free radical intersection match comprise a single target label from one of said target label lists, an explicit intersection match comprises a first target label from a first target label list and a second target label from a second target label list, and said processing unit operates to identify candidate matching label sets by:

for each said target label list, identifying candidate implicit intersection matches;

for each said target label list, identifying candidate free radical intersection matches; and

for each combination of two target label lists, identifying candidate explicit intersection matches.

48. The computer system of claim 47, wherein said processing unit operates to eliminate non-preferred candidate matching label sets by:

a) classifying each target label in each said candidate matching label set as one of the types including a row header, a column header and indeterminate type;

b) for each candidate explicit intersection match;

eliminating said candidate explicit intersection match if said first target label and said second target label are both classified as row headers;

eliminating said candidate explicit intersection match if said first target label and said second target label are both classified as column headers, and

eliminating said candidate explicit intersection match if said first target label and said second target label are located in the same row; and

eliminating said candidate explicit intersection match if said first target label and said second target label are located in the same column;

c) for each candidate explicit intersection match which qualifies as a Single-Double match by having said first target label list containing multiple instances of said first target label and said second target label list containing one instance of said second target label;

if said candidate explicit intersection match is located within the boundaries of an implicit table which includes said formula, eliminating candidate explicit matches that are not located within the boundaries of said implicit table,

if said second target label is a member of a labeled range and at least one instance of said first target label is a member of said labeled range, eliminating candidate explicit intersection matches where said first target label is not a member of said labeled range,

if said second target label is classified as a column header and at least one instance of said first target label is located to the left of said second target label, eliminating candidate matches where said first target label is not located to the left of said second target label,

if said second target label is classified as a row header and at least one instance of said first target label is located to the right of said second target label, eliminating candidate matches where said first target label is not located to the right of said second target label, and

if at least one instance of said first target label can be identified as a closest first target label, said closest first target label being closer to said second target label than other instances of said first target label, eliminating candidate explicit intersection matches which do not comprise one of said closest first target labels;

d) for each candidate explicit intersection match which qualifies as a Double-Double match by having said first target label list containing multiple instances of said first target label and said second target label list containing multiple instances of said second target label;

if said candidate explicit intersection match is located within the boundaries of an implicit table which includes said formula, eliminating candidate explicit matches that are not located within the boundaries of said implicit table,

if said first target label is classified as a column header and said second target label is classified as a row header, and said first target label is positioned to the right of said second target label, eliminating candidate explicit intersection matches which do not meet this criteria,

if at least one instance of said first target label are classified as a row header and can be identified as a closest row header, said closest row header being closer to said formula than other instances of said first target label, eliminating candidate explicit intersection matches which do not comprise one of said closest row headers,

if at least instance of said first target label are classified as a column header and can be identified as a closest column header, said closest column header being closer to said formula than other instances of said first target label, eliminating candidate explicit intersection matches which do not comprise one of said closest column headers, and

if said candidate explicit intersection match can qualify as a Single-Double match, repeating step c with said Single-Double match;

e) for each candidate implicit intersection match and each candidate free radical intersection match having multiple instances of said single target label in said target label list;

if said single target label is classified as a column header and is positioned below said formula, eliminating said candidate implicit intersection match,

if at least one instance of said single target label is positioned within an implicit table which includes said formula, eliminating candidate implicit intersection matches comprising instances of said single target label which are not positioned within said implicit table,

if at least one instance of said single target label is classified as a row header and is positioned to the left of said formula, eliminating candidate implicit intersection matches comprising instances of said single target label which are row headers and are not positioned to the left of said formula,

if at least one instances of said single target label is positioned within the current region for said formula, said current region being the smallest area of cells containing said formula and being surrounded by a rectangle of blank cells, eliminating candidate implicit intersection matches comprising instances of said single target label not positioned within said current region, and

if all instances of said single target label are positioned in the same quadrant about the formula, and if at least one instance of said single target label can be identified as a closest single target label, said closest single target label being closer to said formula than other instances of said single target label, eliminating candidate implicit intersection matches which do not comprise one of said instances of said single target label identified as said closest single target label.

49. The computer system of claim 46, wherein said preferred label set contains a first label in a first row of said spreadsheet and a second label in a second row of said spreadsheet, said first label and said second label being classified as a one type including a row header, a column header or indeterminate type, and said processing unit determines said range of cells by:

if said first label is classified as a row header and said second label is classified as a column header, equating said range of cells to the cell intersected by the row containing said first label and the column containing said second label and which is positioned below said second label;

if said first label is classified as a row header and said second label is classified as indeterminate, equating said range of cells to the cell intersected by the row containing said first label and the column containing said second label and which is positioned below said second label;

if said first label is classified as a column header and said second label is classified as indeterminate, equating said range of cells to the cell intersected by the column containing said first label and the row containing said second label and which is positioned below said first label; and

if said first label and said second label are classified as indeterminate and said first label is positionally above said second label, equating said range of cells to the cell intersected by the column containing said first label and the row containing said second label and which is positioned below said first label.

50. The method of claim 46, wherein said preferred label set contains one label, said label being positioned in a different row and column than said formula, and said processing unit determines said range of cells by:

if said formula is located above said label, equating said range of cells to the cell intersected by the row containing said label and the column containing said formula and which is positioned below said formula;

if said formula is located below and to the left of said label, equating said range of cells to the cell intersected by the column containing said label and the row containing said formula and which is positioned below said label;

if said formula is located below and to the right of said label, then perform the steps of;

classifying said label as one type including a row header, a column header or indeterminate type based on the content of the cells surrounding said label,

if said label is classified as a row header, equating said range of cells to the cell intersected by the row containing said label and the column containing said formula and which is positioned above said formula;

if said label is classified as a column header, equating said range of cells to the cell intersected by the column containing said label and the row containing said formula and which is positioned below said label;

if said label is classified as indeterminate and if the entire current region for said label is located above but not to the left of said formula, equating said range of cells to the cell intersected by the row containing said label and the column containing said formula and which is positioned above said formula; and

in all other cases, equating said range of cells to the cell intersected by the column containing said label and the row containing said formula and which is positioned below said label.

51. The method of claim 46, wherein said preferred label set contains one label sharing a common alignment feature with said formula including a common row or a common column, and said processing unit determines said range of cells by:

if a first current region for said label includes said formula, said first current region being the smallest area of cells containing said label and being surrounded by blank cells, remove said formula from said first current region;

if said label is classified as a column header, and if said label is on the bottom row of said first current region, equating said range of cells to all cells positioned below said label and within a second current region, said second current region being the smallest area of cells containing the next occupied cell positioned below said label and being surrounded by blank cells;

if said label is classified as a column header, and if said label is not on the bottom row of said first current region, identifying said range of cells as comprising all cells positioned below said label and within said first current region;

if said label type is classified as a row header, equating said range of cells to all cells positioned to the right of said label, within the same row as said label and within said first current region, unless at least one exception occurs including;

said label being positioned in the right most column of said first current region,

said formula being positioned immediately to the right of said label, and

the cell immediately to the right of said label containing a blank, said first current region being confined to the column containing said label and the column containing said blank, and a second current region for the cell immediately to the left of said label extending left from said label.

52. A method for automatically inserting label references into a formula of a spreadsheet, said spreadsheet comprising a plurality of cells arranged in a grid of rows and columns, said label references identifying a range of cells, said method comprising the steps of:

enabling an edit mode for entering said formula;

receiving a selection of said range of cells in said spreadsheet to be referenced by said formula;

searching for a range of column headers above said range of cells and spanning said range of cells;

in response to finding said range of column headers,

searching for a range of row headers to the left of said range of cells and spanning said range of cells, and

if said range of row headers is not found, searching for said range of row headers to the right of said range of cells and spanning said range of cells; and

in response to finding said range of column headers and said range of row headers,

extracting a first label from said range of column headers and a second label from said range of row headers, and

inserting said first label and said second label into said formula.

53. The method of claim 52, wherein said extracting step further comprises the steps of:

if said range of column headers spans multiple rows,

setting said first label to include a label in the top, non-blank row of said range of column headers, and

concatenating each label below said label in the top row to said first label, separated by a space;

if said range of row headers spans multiple columns,

setting said second label to include a label in the left-most, non-blank column of said range of row headers, and

concatenating each label to the right of said label in the left-most, non-blank column to said second label, separated by a space;

if said first label is not a proper label, enclose said first label with label boundary marks; and

if said second label is not a proper label, enclose said second label with label boundary marks.

54. In a program module responsive to input commands for entering, modifying and deleting information items in a plurality of information item place-holders, said information items comprising data items, labels and formulas, a method for automatically inserting references to said labels into a formula to identify a range of data items to be operated on by said formula, comprising the steps of:

enabling an edit mode for entering said formula;

selecting said range of cells in said spreadsheet to be referenced by said formula;

searching for a range of column headers above said range of cells and spanning said range of cells;

in response to finding said range of column headers,

searching for a range of row headers to the left of said range of cells and spanning said range of cells, and

if said range of row headers is not found, searching for said range of row headers to the right of said range of cells and spanning said range of cells; and

in response to finding said range of column headers and said range of row headers,

extracting a first label from said range of column headers and a second label from said range of row headers,

inserting said first label and said second label into said formula.

55. The method of claim 54, wherein said extracting step further comprises the steps of:

if said range of column headers spans multiple rows,

setting said first label to include a label in the top row of said range of column headers, and

concatenating each label below said label in the top row to said first label, separated by a space;

setting said second label to said label in said range of row headers;

if said first label is not a proper label, enclose said first label with label boundary marks; and

if said second label is not a proper label, enclose said second label with label boundary marks.

56. A computer-readable medium on which is stored a computer program for automatically inserting label references into a formula of a spreadsheet, said spreadsheet comprising a plurality of cells arranged in a grid of rows and columns, said label references identifying a range of cells, said method comprising the steps of:

enabling an edit mode for entering said formula;

selecting said range of cells in said spreadsheet to be referenced by said formula;

searching for a range of column headers above said range of cells and spanning said range of cells;

in response to finding said range of column headers,

searching for a range of row headers to the left of said range of cells and spanning said range of cells, and

if said range of row headers is not found, searching for said range of row headers to the right of said range of cells and spanning said range of cells; and

in response to finding said range of column headers and said range of row headers,

extracting a first label from said range of column headers and a second label from said range of row headers, and

inserting said first label and said second label into said formula.

57. The method of claim 56, wherein said extracting step further comprises the steps of:

if said range of column headers spans multiple rows,

setting said first label to include a label in the top, non-blank row of said range of column headers, and

concatenating each label below said label in the top, non-blank row to said first label, separated by a space;

setting said second label to said label in said range of row headers;

if said first label is not a proper label, enclose said first label with label boundary marks; and

if said second label is not a proper label, enclose said second label with label boundary marks.

58. A computer system for automatically inserting label references into a formula of a spreadsheet, comprising:

a processing unit;

a memory storage device;

an input device coupled to said processing unit for receiving data;

a pixel-based display device coupled to said processing unit for displaying data;

a program module, stored in said memory storage device for providing instructions to said processing unit;

said processing unit, responsive to said instructions of said program module, operative to;

enable an edit mode for entering said formula;

select a range of cells in said spreadsheet to be referenced by said formula;

search for a range of column headers above said range of cells and spanning said range of cells;

search for a range of row headers to the left of said range of cells and spanning said range of cells, and search for said range of row headers to the right of said range of cells and spanning said range of cells if said range of row headers is not found to the left; and

in response to finding said range of column headers and said range of row headers,

extract a first label from said range of column headers and a second label from said range of row headers, and

insert said first label and said second label into said formula.

59. The computer system of claim 58, wherein said processing unit extracts said first label and said second label by:

if said range of column headers spans multiple rows,

setting said first label to include a label in the top row of said range of column headers, and

concatenating each label below said first label in the top row to said first label, separated by a space;

setting said second label to said label in said range of row headers;

if said first label is not a proper label, enclose said first label with label boundary marks; and

if said second label is not a proper label, enclose said second label with label boundary marks.