U.S. patent application number 11/890785 was filed with the patent office on 2009-02-12 for referring to cells using header cell values.
This patent application is currently assigned to Apple Inc.. Invention is credited to Jay Christopher Capela, Yaniv Gur, Peter William Rapp, Roger Rock Rosner, Christopher Douglas Weeldreyer.
Application Number | 20090044090 11/890785 |
Document ID | / |
Family ID | 40347619 |
Filed Date | 2009-02-12 |
United States Patent
Application |
20090044090 |
Kind Code |
A1 |
Gur; Yaniv ; et al. |
February 12, 2009 |
Referring to cells using header cell values
Abstract
Referring to cells using header cell values is disclosed. In
some embodiments, a header cell value of a header cell is allowed
to be used to refer to one or more other cells that are associated
with the header cell. The header cell may be included in a header
row or column included in a table. A header row cell value may be
employed to refer to one or more other cells in a corresponding
column, and a header column cell value may be employed to refer to
one or more other cells in a corresponding row.
Inventors: |
Gur; Yaniv; (Pittsburgh,
PA) ; Capela; Jay Christopher; (Santa Cruz, CA)
; Rapp; Peter William; (Pittsburgh, PA) ; Rosner;
Roger Rock; (Mountain View, CA) ; Weeldreyer;
Christopher Douglas; (San Carlos, CA) |
Correspondence
Address: |
VAN PELT, YI & JAMES LLP AND APPLE COMPUTER, INC.
10050 N. FOOTHILL BOULEVARD, SUITE 200
CUPERTINO
CA
95014
US
|
Assignee: |
Apple Inc.
|
Family ID: |
40347619 |
Appl. No.: |
11/890785 |
Filed: |
August 6, 2007 |
Current U.S.
Class: |
715/212 |
Current CPC
Class: |
G06F 40/18 20200101;
G06F 40/177 20200101 |
Class at
Publication: |
715/212 |
International
Class: |
G06F 15/00 20060101
G06F015/00 |
Claims
1. A method for referring to cells of a table, comprising:
receiving a header cell value for a header cell; and enabling the
header cell value to be used to refer to one or more other cells
that are associated with the header cell.
2. A method as recited in claim 1, further comprising receiving an
indication to include one or more of a header column and a header
row in the table.
3. A method as recited in claim 1, wherein the header cell
comprises a header row cell.
4. A method as recited in claim 3, wherein enabling the header cell
value to be used to refer to one or more other cells that are
associated with the header cell comprises enabling the header row
cell value to be used to refer to one or more other cells included
in a corresponding column.
5. A method as recited in claim 1, wherein the header cell
comprises a header column cell.
6. A method as recited in claim 5, wherein enabling the header cell
value to be used to refer to one or more other cells that are
associated with the header cell comprises enabling the header
column cell value to be used to refer to one or more other cells
included in a corresponding row.
7. A method as recited in claim 1, wherein receiving a header cell
value for a header cell comprises receiving a plurality of header
cell values including the header cell value for a plurality of
corresponding header cells.
8. A method as recited in claim 1, further comprising enabling a
header row cell value associated with a column and a header column
cell value associated with a row to be used to refer to a cell that
is the intersection of the column and row.
9. A method as recited in claim 8, wherein a syntax of a reference
to the cell comprises the header row cell value and the header
column cell value separated by a space.
10. A method as recited in claim 9, wherein the syntax further
comprises one or more of a document name, a sheet name, and a table
name associated with the cell.
11. A method as recited in claim 1, further comprising enabling the
header cell value to be used in combination with one or more
address tab values to refer to one or more cells.
12. A method as recited in claim 1, further comprising providing
options to hide or show one or more dimensions of address tabs with
respect to the table.
13. A method as recited in claim 1, further comprising receiving a
reference in a formula comprising the header cell value.
14. A method as recited in claim 1, wherein when used to refer to a
corresponding column or row, the header cell value refers to only
associated body cells and excludes the header cell, an associated
footer cell, or both.
15. A method as recited in claim 14, wherein the associated footer
cell is included in a footer row optionally included in the
table.
16. A method as recited in claim 1, further comprising providing
options to include or hide one or more of a header row, a header
column, and a footer row in the table.
17. A method as recited in claim 1, further comprising enabling one
or more cells to be referred to using one or more of a document
name, a sheet name, a table name, and the header cell value.
18. A method as recited in claim 1, further comprising receiving an
indication that a formula has been entered into the header
cell.
19. A method as recited in claim 18, wherein the formula entered
into the header cell includes a reference to another header cell of
a same dimension.
20. A method as recited in claim 18, further comprising using the
formula entered into the header cell to populate a corresponding
column or row of cells.
21. A method as recited in claim 20, wherein using the formula
entered into the header cell to populate a corresponding column of
cells comprises excluding an associated footer cell, if any, from
being populated.
22. A method as recited in claim 1, wherein the table is included
in a sheet of a spreadsheet application.
23. A method as recited in claim 1, wherein the table comprises a
sheet of a spreadsheet application.
24. A system for referring to cells of a table, comprising: a
processor configured to: receive a header cell value for a header
cell; and enable the header cell value to be used to refer to one
or more other cells that are associated with the header cell; and a
memory coupled to the processor and configured to provide
instructions to the processor.
25. A system as recited in claim 24, wherein the header cell
comprises a header row cell.
26. A system as recited in claim 25, wherein to enable the header
cell value to be used to refer to one or more other cells that are
associated with the header cell comprises to enable the header row
cell value to be used to refer to one or more other cells included
in a corresponding column.
27. A system as recited in claim 24, wherein the header cell
comprises a header column cell.
28. A system as recited in claim 27, wherein to enable the header
cell value to be used to refer to one or more other cells that are
associated with the header cell comprises to enable the header
column cell value to be used to refer to one or more other cells
included in a corresponding row.
29. A system as recited in claim 24, wherein when used to refer to
a corresponding column or row, the header cell value refers to only
associated body cells and excludes the header cell, an associated
footer cell, or both.
30. A system as recited in claim 24, wherein the processor is
further configured to receive an indication that a formula has been
entered into the header cell.
31. A system as recited in claim 30, wherein the processor is
further configured to use the formula entered into the header cell
to populate a corresponding column or row of cells.
32. A computer program product for referring to cells of a table,
the computer program product being embodied in a computer readable
medium and comprising computer instructions for: receiving a header
cell value for a header cell; and enabling the header cell value to
be used to refer to one or more other cells that are associated
with the header cell.
33. A computer program product as recited in claim 32, further
comprising computer instructions for receiving an indication to
include one or more of a header column and a header row in the
table.
34. A computer program product as recited in claim 32, wherein the
header cell comprises a header row cell.
35. A computer program product as recited in claim 34, wherein
enabling the header cell value to be used to refer to one or more
other cells that are associated with the header cell comprises
enabling the header row cell value to be used to refer to one or
more other cells included in a corresponding column.
36. A computer program product as recited in claim 32, wherein the
header cell comprises a header column cell.
37. A computer program product as recited in claim 36, wherein
enabling the header cell value to be used to refer to one or more
other cells that are associated with the header cell comprises
enabling the header column cell value to be used to refer to one or
more other cells included in a corresponding row.
38. A computer program product as recited in claim 32, wherein
receiving a header cell value for a header cell comprises receiving
a plurality of header cell values including the header cell value
for a plurality of corresponding header cells.
39. A computer program product as recited in claim 32, further
comprising computer instructions for enabling a header row cell
value associated with a column and a header column cell value
associated with a row to be used to refer to a cell that is the
intersection of the column and row.
40. A computer program product as recited in claim 32, wherein when
used to refer to a corresponding column or row, the header cell
value refers to only associated body cells and excludes the header
cell, an associated footer cell, or both.
41. A computer program product as recited in claim 32, further
comprising computer instructions for receiving an indication that a
formula has been entered into the header cell.
42. A computer program product as recited in claim 41, further
comprising computer instructions for using the formula entered into
the header cell to populate a corresponding column or row of cells.
Description
BACKGROUND OF THE INVENTION
[0001] In typical spreadsheet applications, cells are referred to
or addressed using column (e.g., A, B, C, etc.) and row (e.g., 1,
2, 3, etc.) address tabs. Each cell is uniquely identified by a
cell address that reflects the column and row address tabs,
respectively, of the column and row in which the cell is located
(e.g., A1). Headings or labels may be entered into one or more
cells to give meaning to data included in a corresponding column or
row. For example, if the cells of column A are to include revenue
data, the top cell in the column (e.g., cell A1) may be labeled as
"REVENUE".
BRIEF DESCRIPTION OF THE DRAWINGS
[0002] Various embodiments of the invention are disclosed in the
following detailed description and the accompanying drawings.
[0003] FIG. 1A illustrates an embodiment of a structure of a
table.
[0004] FIG. 1B illustrates an embodiment of a table.
[0005] FIG. 1C illustrates an embodiment in which address tabs are
provided with a table.
[0006] FIG. 1D illustrates an embodiment in which a header row cell
value is employed to refer to a corresponding column of a table in
a formula.
[0007] FIG. 1E illustrates the value resulting from the formula of
FIG. 1D.
[0008] FIG. 2A illustrates an embodiment of a sheet of a document
of a spreadsheet application.
[0009] FIG. 2B illustrates an embodiment of a sheet of a document
of a spreadsheet application.
[0010] FIG. 2C illustrates an embodiment of the auto-completion of
a column name.
[0011] FIG. 2D illustrates an embodiment of the auto-completion of
a row name.
[0012] FIG. 2E illustrates an embodiment of the auto-completion of
a table name.
[0013] FIG. 2F illustrates an embodiment of the auto-completion of
a sheet name.
[0014] FIG. 3 illustrates an embodiment of a process for
auto-completing a name.
[0015] FIG. 4A illustrates an embodiment of a table.
[0016] FIG. 4B illustrates an embodiment of entering a formula into
a header cell of a table.
[0017] FIG. 4C illustrates the values resulting from the formula of
FIG. 4B.
[0018] FIG. 4D illustrates an embodiment of entering a formula into
a header cell that includes a reference to another header cell.
[0019] FIG. 4E illustrates the values resulting from the formula of
FIG. 4D.
[0020] FIG. 5A illustrates an embodiment of a table.
[0021] FIG. 5B illustrates an embodiment of entering a formula
editing mode in a header row cell associated with a column of a
table.
[0022] FIG. 5C illustrates an embodiment of inserting a reference
to another column of a table in the formula of a host header row
cell by clicking on a cell of that column.
[0023] FIG. 5D illustrates the completion of the formula of FIG.
5C.
[0024] FIG. 5E illustrates the values resulting from the formula of
FIG. 5D.
[0025] FIG. 5F illustrates an embodiment of entering a formula
editing mode in a header column cell associated with a row of a
table.
[0026] FIG. 5G illustrates an embodiment of inserting a reference
to another row of a table in the formula of a host header column
cell by clicking on cells of that row.
[0027] FIG. 5H illustrates the completion of the formula of FIG.
5G.
[0028] FIG. 5I illustrates the values resulting from the formula of
FIG. 5H.
[0029] FIG. 5J illustrates an embodiment of entering a formula
editing mode in a cell of a table.
[0030] FIG. 5K illustrates an embodiment of inserting a reference
to another cell of a table into the formula of a host cell by
clicking on the cell.
[0031] FIG. 5L illustrates the value resulting from the formula of
FIG. 5K.
[0032] FIG. 5M illustrates an embodiment of entering a formula
editing mode in a cell of a table.
[0033] FIG. 5N illustrates an embodiment of inserting a reference
to an entire row of a table into the formula of a host cell by
clicking on the header cell of the row.
[0034] FIG. 5O illustrates the completion of the formula of FIG.
5N.
[0035] FIG. 5P illustrates the value resulting from the formula of
FIG. 5O.
[0036] FIG. 6 illustrates an embodiment of a process for inserting
references in a formula being entered into a host cell.
DETAILED DESCRIPTION
[0037] The invention can be implemented in numerous ways, including
as a process, an apparatus, a system, a composition of matter, a
computer readable medium such as a computer readable storage medium
or a computer network wherein program instructions are sent over
optical or communication links. In this specification, these
implementations, or any other form that the invention may take, may
be referred to as techniques. A component such as a processor or a
memory described as being configured to perform a task includes
both a general component that is temporarily configured to perform
the task at a given time or a specific component that is
manufactured to perform the task. In general, the order of the
steps of disclosed processes may be altered within the scope of the
invention.
[0038] A detailed description of one or more embodiments of the
invention is provided below along with accompanying figures that
illustrate the principles of the invention. The invention is
described in connection with such embodiments, but the invention is
not limited to any embodiment. The scope of the invention is
limited only by the claims and the invention encompasses numerous
alternatives, modifications and equivalents. Numerous specific
details are set forth in the following description in order to
provide a thorough understanding of the invention. These details
are provided for the purpose of example and the invention may be
practiced according to the claims without some or all of these
specific details. For the purpose of clarity, technical material
that is known in the technical fields related to the invention has
not been described in detail so that the invention is not
unnecessarily obscured.
[0039] Referring to cells using header cell values is disclosed. In
some embodiments, a header cell value of a header cell is allowed
to be used to refer to one or more other cells that are associated
with the header cell. The header cell may be included in a header
row or column included in a table. A header row cell value may be
employed to refer to one or more other cells in a corresponding
column, and a header column cell value may be employed to refer to
one or more other cells in a corresponding row.
[0040] As used herein, the term "cell" refers to a cell associated
with at least standard spreadsheet functionality. The term "at
least standard spreadsheet functionality" in the context of a cell
includes the ability to define the content of one cell in such a
way that the content of the one cell is determined based at least
in part on the content of one or more other cells, and the content
of the one cell is updated automatically without further human
action if the content of one or more of the one or more other cells
is changed. As used herein, the term "host cell" refers to a cell
in a formula editing mode, i.e., a cell into which a formula is
being entered. In some embodiments, cells in a spreadsheet
application are organized into one or more individual tables or
table objects, each of which includes a desired number of rows and
columns of cells. In such cases, a sheet of a spreadsheet document
may include a desired number of table objects. Although in many of
the examples described herein the cells of a spreadsheet
application are organized into such table objects, the techniques
described herein may be similarly employed with respect to other
cell configurations or organizations, such as the (seemingly)
infinite single grid or table of cells in each sheet of some
typical spreadsheet applications that includes an unlimited (or
very large) number of rows and columns of cells. Although many of
the examples provided herein are with respect to a spreadsheet
application, the techniques described herein may be similarly
employed with respect to any application, spreadsheet or
otherwise.
[0041] In some embodiments, one or more of a header row, header
column, and footer row may be included in a table. FIG. 1A
illustrates an embodiment of a structure of a table. In the given
example, table 100 includes a header row 102, a header column 104,
a footer row 106, and five rows and four columns of body cells 108.
Using a header cell value, e.g., in a formula, to refer to or
address one or more other cells associated with the header cell is
disclosed. In some embodiments, a header row cell value can be
employed to refer to one or more cells in a corresponding column of
a table, and a header column cell value can be employed to refer to
one or more cells in a corresponding row of a table. Any name or
value may be entered into a header cell to refer to a corresponding
column or row of cells. Header cell names or values can be selected
to provide a more intuitive way to refer to the rows, columns,
and/or cells of a table. In various embodiments, header cell values
may be user defined, may be selected from a predefined list of
values, etc. A table may include any combination of a header row, a
header column, and a footer row, if any. Options may exist with
respect to a table to include or hide (i.e. turn on or off) a
header row, a header column, and/or a footer row. In some
embodiments, footer row cells are employed to enter formulas. FIG.
1B illustrates an embodiment of a table 100 that includes a header
row 102, a header column 104, and a footer row 106. In the given
example, values have been entered into the header cells, and data
has been entered into the body cells. In some embodiments, address
tabs are provided with a table. FIG. 1C illustrates an embodiment
in which address tabs are provided with table 100 of FIG. 1B.
[0042] In some embodiments, if specified, one or more header cell
values can be used to refer to or address the rows, columns, and/or
cells of a table in a manner analogous to address tabs. For
example, with respect to FIGS. 1B and 1C, "Milan" can be used to
refer to non-header cells in row 3 (i.e., cells B3 through E3), and
"Q3" can be used to refer to the non-header cells in column D
(i.e., in some embodiments cells D2 through D6 and in some
embodiments cells D2 through D7). Similarly, "Milan Q3" or "Q3
Milan" can be used to refer to cell D3, i.e. the cell that is the
intersection of row Milan and column Q3. In some embodiments, such
as in the aforementioned example, a cell of a table can be referred
to using the header row and column cell values with which it is
associated. In some embodiments, one syntax used to make such a
reference includes separating the associated row and column names
by a space. In such cases, the space between the row and column
(e.g., "Milan Q3") or column and row (e.g., "Q3 Milan") names
implies an intersection between the associated row and column or
column and row, respectively. In other embodiments, any appropriate
syntax can be employed to refer to or specify cells using their
associated row and column names. In some embodiments, cell ranges
can be specified using header cell values similar to the manner in
which cell ranges are specified using address tabs. For example,
Paris Q1:London Q4 can be used to specify cell range B2:E4.
[0043] Address tabs may be displayed with respect to a table such
as in FIG. 1C, and the rows, columns, and/or cells of the table may
be referred to by corresponding address tabs even when a header row
and/or column are included in a table and the associated header
cell values are specified. In various embodiments, the rows,
columns, and cells of a table may be referred to using any
appropriate combination of header cell values (if specified) and/or
address tabs, using any appropriate syntax. For example, body cell
D3 in the example of FIG. 1C, in various embodiments, may be
referred to as "D3", "3D", "D Milan", "Milan D", "3Q3", "Q3 3",
"Milan Q3", and "Q3 Milan". In some embodiments, options to turn on
or off one or more dimensions of address tabs of a table are
provided. For example, it may be desirable to turn off both (row
and column) dimensions of address tabs of a table that includes a
header row and column, such as depicted for table 100 in the
example of FIG. 1B. Examples of situations in which a user may
desire to turn off one or both of the column address tabs and row
address tabs include where a header row and/or column is included
and the user prefers to use the header row and/or column values to
identify cells, for example because the user finds those names to
be more intuitive or otherwise more useful and/or easier to
use.
[0044] FIG. 1D illustrates an embodiment in which a header row cell
value is employed to refer to a corresponding column of a table. In
the example of FIG. 1D, header cell value "Q1" is used in the
argument of the "SUM" function to specify the body cells of column
Q1. The formula "=SUM(Q1)" is easier to enter and more intuitive
than a corresponding formula that uses address tabs, i.e.
"=SUM(B2:B6)" with respect to corresponding address tabs such as
those depicted in FIG. 1C. FIG. 1E illustrates the value resulting
from the formula of FIG. 1D. Although in the example of FIG. 1D a
formula is entered into a cell of the footer row, a formula may be
similarly entered into any cell of table 100. Other header cell
values may be similarly employed to refer to the rows, columns,
and/or cells of a table. For example, the total annual revenue for
London can be computed using the formula "=SUM(London)", and the
total fourth quarter European revenue can be computed using the
formula "=Paris Q4+Milan Q4+London Q4". Such formulas that use
header cell values are cleaner and more user-friendly than
corresponding formulas that use address tabs, e.g., "=SUM(B4:E4)"
and "=E2+E3+E4", respectively, with respect to corresponding
address tabs such as those depicted in FIG. 1C.
[0045] In some embodiments, when used to refer to a corresponding
column or row, a header cell value refers to only body cells
associated with the corresponding column or row. For example, in
the formula "=SUM(Q1)" in the example of FIG. 1D, the header cell
value "Q1" refers to only the body cells of column Q1 and does not
include the header cell or footer cell associated with column Q1.
Similarly, in the formula "=SUM(London)", the header cell value
"London" refers to only the body cells of row London and does not
include the header cell associated with the row. Since when used to
refer to a corresponding column or row a header cell value includes
all body cells of the corresponding column or row, a column or row
reference in a formula such as "Q1" in "=SUM(Q1)" or "London" in
"=SUM(London)" does not have to be updated when an associated table
is resized since the reference stays the same. For example, if
additional rows of data associated with new locations were to be
added to table 100 of FIG. 1E, the column reference "Q1" in formula
"=SUM(Q1)" would not have to be updated as "Q1" would include all
body cells of column Q1 including the added cells. Various
advantages exist by having a header cell value refer to only
associated body cells. For example, with the header cell value
excluded, a header cell value such as "2007" does not interfere
with an intended calculation or result. With footer row cells
excluded, a formula such as "=SUM(Q1)" can be included in the same
column Q1 without creating a circular reference.
[0046] Although using header cell values to refer to or address the
rows, columns, and/or cells of a table when in a formula editing
mode has been described in the given examples, header cell values
can be similarly employed to refer to the rows, columns, and/or
cells of a table in any other context such as, for example, when
specifying source data when creating a chart or graph from data
included in one or more tables.
[0047] As described above, the rows, columns, and/or cells of a
table may be addressed or referred to using associated address tabs
and/or header cell values. Like unique address tabs that are
associated with each row and column, unique header cell values need
to be selected for header cells included in a table to be able to
uniquely identify the rows, columns, and/or cells of the table
using associated header cell values. A spreadsheet document or file
may include one or more sheets (or canvases), and each sheet (or
canvas) may include one or more tables and/or other objects. In
some embodiments, each table included in a sheet is uniquely named
so that the rows, columns, and cells included in each table can be
uniquely identified. In some embodiments, an option to show or hide
the table name with a table is provided. For example, the table
name is hidden in the examples of FIGS. 1B-1D, but table names are
shown or unhidden in the examples of FIGS. 2A-2F. Since the name of
each table in a sheet is unique, multiple tables on the same sheet
may have one or more identical header cell values. Since each sheet
of a spreadsheet document is uniquely named, multiple sheets of a
spreadsheet document may have tables of the same name and possibly
with one or more identical header cell values. The rows, columns,
and/or cells of a table can be referred to in another table or
object (e.g., a chart, graph, etc.) included in the same sheet or a
different sheet of a spreadsheet document. In order to uniquely
identify or refer to one or more rows, columns, and/or cells of a
table in another table or object on the same sheet or on a
different sheet, the associated table name and/or sheet name may be
specified in addition to associated header cell value(s) (and/or
address tab value(s)). A syntax employed in some embodiments to
uniquely refer to a row, column, and cell of a table is provided in
Table 1.
TABLE-US-00001 TABLE 1 Table Compo- nent Syntax Row
<SheetName>::<TableName>::<RowName> Column
<SheetName>::<TableName>::<ColumnName> Cell
<SheetName>::<TableName>::<RowName>
<ColumnName> or
<SheetName>::<TableName>::<ColumnName>
<RowName>
In the syntax of Table 1, header cell and/or address tab values may
be employed for the row name and column name parameters. In some
embodiments, in the cases in which one or more address tab values
are used for the row name and/or column name parameters in the
syntax provided for a cell in Table 1, the space between the row
name and column name parameters may be omitted. The sheet name
and/or table name parameters may be included to uniquely refer to a
row, column, or cell of a table. In various embodiments, the sheet
name and/or table name parameters may be omitted if a row, column,
and/or cell being referred to can be uniquely identified without
one or more of those parameters. In some embodiments, when
including a reference to a row, column, or cell of a table in a
cell of the same table, the table name and/or sheet name parameters
may be omitted; and when including a reference to a row, column, or
cell of a table in a cell of a different table in the same sheet
and/or in a different object in the same sheet, the sheet name
parameter (and in some embodiments also the table name parameter,
e.g., if the header cell value(s) in the reference are unique at
least within the associated sheet) may be omitted. When including a
reference to a row, column, or cell of a table in a cell of a
different table in a different sheet and/or in a different object
in a different sheet, in some embodiments, the sheet name parameter
may be omitted, e.g., if an associated table name parameter that is
included in the reference is unique across all sheets of a
document, and in some embodiments the table name parameter may also
be omitted if the header cell value(s) in the reference are unique
across all tables (and/or other objects) in all sheets. In some
embodiments, even though redundant and/or unnecessary, the sheet
name and/or table name parameters may be included in a reference.
In the cases in which the row name and/or column name parameters
are specified using address tabs, the table name and/or sheet name
may need to be specified. In some embodiments, a row, column,
and/or cell of a table in one document can be referred to in
another document by further including a document name parameter in
a reference, e.g., by adding <DocumentName> to the syntax of
Table 1. Table 1 provides one embodiment of a syntax that can be
employed to uniquely identify or refer to the rows, columns, and
cells of a table in a spreadsheet application. Any other
appropriate syntax may be employed in other embodiments.
[0048] FIGS. 2A and 2B illustrate embodiments of two sheets,
associated with tabs 202 and 204, respectively, of a document of a
spreadsheet application. The sheet associated with tab 202, the
contents of which are shown in FIG. 2A, is named "2007" and
includes three tables: January 206, February 207, and Specials 208.
The sheet associated with tab 204, the contents of which are shown
in FIG. 2B, is named "2006" and also includes three tables: January
210, February 212, and March 214. In the given examples, each table
in each sheet is uniquely named (January, February, Specials,
March), but all tables except the Specials table 208 comprise the
same table structure and include the same header cell values (Bags,
Shoes, and Sales). Both sheets include tables named January and
February. The rows, columns, and cells of any of the tables can be
uniquely identified or referred to using a syntax such as the
syntax of Table 1.
[0049] In some embodiments, while a user is entering or typing a
sheet name, table name, row name, and/or column name, for example,
when entering a reference in a formula using a syntax such as that
provided in Table 1, auto-completion of the name being typed is
available and/or provided. In the cases in which multiple potential
matches exist for the name being typed, an interface that includes
the potential matches is presented from which a desired value can
be selected by the user. In various embodiments, the
auto-completion feature is or can be configured to be available
once a prescribed number of the first few characters of a name have
been typed. In some embodiments, the auto-completion feature is
automatically provided after a prescribed number of the first few
characters of a name have been typed. In some embodiments, the
auto-completion feature is provided in response to a user typing at
least a prescribed number of the first few characters of a name
followed by one or more arrow keys. In some embodiments, the
auto-completion feature is available on the third keystroke (i.e.
starting with the third character of a name), and if multiple
matches exist, a drop down list of matching auto-completion options
is provided. In some embodiments, an option to turn the
auto-completion feature on or off is provided. In some embodiments,
in addition to appending the completion of the name being typed,
the auto-completion feature prepends extra information or
identifiers such as a document name, sheet name, and/or table name,
for example, to provide further disambiguation when the same (row,
column, and/or table) name is used multiple times.
[0050] FIG. 2C illustrates an embodiment of the auto-completion of
a column name. In the given example, the characters "Sa" are
entered into a formula being entered into a cell of table 206 of
sheet 202. The auto-completion feature is provided (e.g.,
automatically or in response to, for example, left or down arrow
keystrokes by the user) starting with the third character of the
name being entered. Since multiple potential matches exist in the
given example, a list 216 of potential matches is provided from
which a desired completion can be selected. In addition to
appending completions, the auto-completion feature prepends
additional parameters, such as the sheet name and/or table name in
the example of FIG. 2C to distinguish the same column name
("Sales") used in different tables and/or sheets. The
auto-completion feature provides the user with easy access to the
Sales column in the same table (i.e. "Sales" in list 216), in other
tables on the same sheet (i.e. "February::Sales" in list 216), and
in other tables on different sheets (i.e. "2006::January::Sales",
"2006::February::Sales", and "March::Sales" in list 216). Selection
of "Sales" in list 216 results in the completion "les" to be
appended to the entered characters "Sa".
[0051] In some embodiments, such as in the example of FIG. 2C, the
sheet name is omitted if a table name appears only once in a
document. Thus, in the given example, the sheet name "2006" is not
included with "March::Sales" in list 216. In some embodiments, if a
table name appears multiple times on different sheets of a
document, the sheet name is included for a table that is not on the
current sheet. In some embodiments, the sheet name is included for
any table not on the current sheet. In such a case, "March::Sales"
in list 216 would be "2006::March::Sales". In some embodiments,
such as in the example of FIG. 2C, if a row or column name appears
multiple times in different tables on a sheet, the table name is
included for each table that is not the current table. Thus, in the
given example, the table name "February" is included in
"February::Sales" in list 216. In some embodiments, the table name
is omitted if a row or column name appears only once in a sheet.
Thus, in the given example, the table name "Specials" is not
prepended to "Save" in list 216. In some embodiments, the table
name is included for any row or column name not of the current
table. In such a case, "Save" in list 216 would be
"Specials::Save".
[0052] Although described with respect to a column name in FIG. 2C,
the auto-completion feature is in various embodiments similarly
available for row names, table names, and/or sheet names. FIG. 2D
illustrates an embodiment of the auto-completion of a row name. In
the example of FIG. 2D, since a single auto-completion match
exists, it is provided inline. In some embodiments in the cases in
which multiple potential matches exist such as in FIG. 2C, the most
likely match is by default selected (i.e. highlighted) in an
associated list, and the associated completion is appended to the
entered characters inline. In some embodiments, such a most likely
match is, for example, alphabetically selected from the one or more
options associated with the current table and/or sheet. For
example, in FIG. 2C, "Sales" may be by default selected since it is
the only option with respect to the current table 206. Of course,
in various embodiments, such a default selection may not be
selected by a user who may opt to select a different option or may
not select any of the provided options. FIG. 2E illustrates an
embodiment of the auto-completion of a table name. FIG. 2F
illustrates an embodiment of the auto-completion of a sheet name.
In the example of FIG. 2F, a single match ("2006") exists because
the sheet name of the current sheet ("2007") does not have to be
(redundantly) specified in the current sheet.
[0053] FIG. 3 illustrates an embodiment of a process for
automatically completing a remainder portion of a name as it is
being entered. In some embodiments, process 300 is employed with
respect to FIGS. 2C-2F. Process 300 starts at 302 at which at least
a prescribed number of starting characters of a name (or other
identifier) being entered are received. In some embodiments, the
prescribed number of starting characters comprises the first two
characters of the name. In some embodiments, the name is being
entered into a host cell into which a formula is being entered as a
reference name, e.g., in the formula, to refer to one or more other
cells. In various embodiments, the name comprises one or more of a
row name, column name, table name, sheet name, and document name.
At 304, one or more matching auto-completion options, if any, are
provided, and process 300 ends. In some embodiments, 304 includes
determining a set of one or more valid reference names that begin
with the received starting characters, if any, so that they can be
provided at 304 as matching auto-completion options. In such cases,
a valid reference name is one that identifies using a supported
syntax (e.g., the syntax of Table 1) a spreadsheet document or a
portion of a spreadsheet document and may include one or more of a
row name, column name, table name, sheet name, and document name.
One of the provided auto-completion options, if any, may be
selected by a user to complete the name being entered.
[0054] Although an auto-completion feature is described with
respect to entering a reference in a formula in the given examples,
in various embodiments, the auto-completion feature may be
available and can similarly be employed when entering names or
references in any other context such as, for example, when
specifying source data when creating a chart or graph. Although
described with respect to table objects, such an auto-completion
feature may be similarly employed with respect to the names of
other types of objects or parts thereof that are included in a
sheet of a spreadsheet application, such as charts, graphs shapes,
images, graphics, multimedia content, etc.
[0055] In some embodiments, a formula may be entered into a header
cell to populate the body cells of a corresponding row or column.
FIGS. 4A-4E illustrate examples of entering formulas into header
row cells to populate corresponding columns. FIG. 4A illustrates an
embodiment of a table 400 that includes a header row 402 and a
header column 404. FIG. 4B illustrates an embodiment of entering a
formula into the Speakers header cell of table 400. Unlike other
cell types, in some embodiments, a formula may be entered into a
header cell even though the header cell includes a name or value
(e.g., "Speakers", "Tables", "Section1", etc.). In some
embodiments, a formula editing mode is entered with respect to a
host cell (e.g., the Speakers header cell in the example of FIG.
4B) by clicking on or otherwise selecting the cell and entering an
equals sign "=". As depicted, the formula "=2" is entered into the
Speakers header cell. The resulting population of the body cells
associated with the Speakers column of table 400 is shown in FIG.
4C. As illustrated, all cells of the Speakers column of table 400
are populated with the value "2". In some embodiments, a formula
entered into a header cell may refer to one or more other header
cells of the same dimension. In such cases, values of the body
cells of the header cells that are referred to in the formula are
employed in the computation of the values of corresponding body
cells associated with the header cell into which the formula is
being entered. FIG. 4D illustrates an embodiment of entering into a
header cell a formula that includes a reference to another header
cell. As depicted, the formula "=Tables*6" is entered into the
Chairs header cell of table 400 and includes a reference to the
Tables header cell. In some embodiments, the Tables reference is
inserted into the formula by clicking on or otherwise selecting the
Tables header cell (or an address tab associated with the Tables
column) while in the formula editing mode in the Chairs header
cell. Alternatively, the Tables references may be manually entered
into the formula. The resulting population of the body cells
associated with the Chairs column of table 400 due to the formula
of FIG. 4D is shown in FIG. 4E. As illustrated, the value of each
cell in each row of the Chairs column is the value of the cell in
the corresponding row of the Tables column multiplied by six. In
some embodiments, a formula entered into a header row cell only
populates body cells of the associated column and does not populate
a footer cell of the associated column if a footer row is included
in the table. Although entering formulas into header row cells to
populate the columns of a table is depicted in the given examples,
similar techniques can be employed to enter formulas into header
column cells to populate corresponding rows of a table. In such
cases, a formula entered into a header column cell may include a
reference to one or more other header column cells. In some
embodiments, a formula is entered into a header cell into which a
name or value has not (yet) been entered.
[0056] Formulas may be entered into different types of spreadsheet
cells such as header cells, body cells, and footer cells. When in a
formula editing mode in a host cell, references to one or more
cells or cell ranges that are clicked on or otherwise selected
while in the formula editing mode are inserted into the formula
being entered into the host cell. As disclosed herein, in some
embodiments, references to one or more cells or cell ranges that
are clicked on or otherwise selected are added to a formula being
entered into a host cell based on the zones (e.g., within an
associated table) or types of the clicked cell(s) and/or the host
cell. For example, in some embodiments, while in a formula editing
mode in a header row cell associated with a column of a table,
clicking on or otherwise selecting a cell that is in a different
column results in the insertion of a reference to the header row
cell associated with the selected cell in the formula of the host
cell. Likewise, in some embodiments, while in a formula editing
mode in a header column cell associated with a row of a table,
clicking on or otherwise selecting a cell that is in a different
row results in the insertion of a reference to the header column
cell associated with the selected cell in the formula of the host
cell. In some embodiments, a spreadsheet application can be
configured such that clicking on or otherwise selecting one or more
non-header cells while in a formula editing mode in a header cell
results in the insertion of a reference to the selected cell(s)
(e.g., rather than a reference to an associated row or column) in
the formula being entered into the host cell. In some embodiments,
when entering a formula into a body or footer cell, clicking on or
otherwise selecting one or more other body and/or footer cells
results in references to those cells being inserted into the
formula being entered into the host cell but clicking on or
otherwise selecting a header cell results in a reference to the
entire row or column associated with the header cell to be inserted
into the formula. In some such cases, the reference to the entire
row or column includes only the body cells of the row or column.
FIGS. 5A-5P illustrate examples of reference adding behavior based
on the zones or types of the host cell and clicked cell(s).
[0057] FIG. 5A illustrates an embodiment of a table 500 that
includes a header row 502 and a header column 504. FIG. 5B
illustrates an embodiment of entering a formula editing mode in a
header row cell associated with a column of table 500. As depicted
in the example of FIG. 5B, a formula editing mode may be entered,
for example, by clicking on or otherwise selecting header row cell
Blue and entering an equals sign "=". FIG. 5C illustrates an
embodiment of inserting a reference to another column of table 500
in the formula of host header cell Blue by clicking on a cell of
that column. As depicted, a reference to header cell Red is
inserted into the formula of host header cell Blue by clicking on
cell Red Circles, a cell included in column Red. In the given
example, a reference to the entire column (i.e. Red) of the clicked
cell is inserted into the formula rather than a reference to the
clicked cell (i.e. Red Circles) since the host cell is a header row
cell. Completion of the formula being entered into header cell Blue
of table 500 is depicted in FIG. 5D, and the resulting values of
the cells of column Blue are depicted in FIG. 5E.
[0058] FIG. 5F illustrates an embodiment of entering a formula
editing mode in a header column cell (Rectangles) associated with a
row of table 500. FIG. 5G illustrates an embodiment of inserting a
reference to another row of table 500 in the formula of host header
cell Rectangles by selecting a range of cells of that row. As
depicted, a reference to header cell Squares is inserted into the
formula of host header cell Rectangles by selecting cells Yellow
Squares and Blue Squares, cells included in row Squares. In the
given example, a reference to the entire row (i.e. Squares)
associated with the clicked cells is inserted into the formula
rather than a reference to the clicked cell range (e.g., Yellow
Squares:Blue Squares) since the host cell is a header column cell.
Completion of the formula being entered into header cell Rectangles
of table 500 is depicted in FIG. 5H, and the resulting values of
the cells of row Rectangles are depicted in FIG. 5I.
[0059] FIG. 5J illustrates an embodiment of entering a formula
editing mode in a non-header cell (Blue Triangles) of table 500.
FIG. 5K illustrates an embodiment of inserting a reference to
another cell of table 500 into the formula of host cell Blue
Triangles by clicking on the cell. As depicted, a reference to cell
Red Triangles is inserted into the formula of host cell Blue
Triangles by clicking on cell Red Triangles. The value resulting
from the formula entered into cell Blue Triangles of table 500 in
FIG. 5K is depicted in FIG. 5L. In some embodiments, the behavior
shown in this example (FIGS. 5J-5L) differs from that illustrated
by FIGS. 5B-C, for example, because the formula is being entered
into a cell that is not a header cell, which results in a reference
to the particular cell that is selected, as opposed to a reference
to the column (or row, in the case of a formula being entered in a
header cell of a header column) in which the selected cell is
located, to be inserted into the formula.
[0060] FIG. 5M illustrates an embodiment of entering a formula
editing mode in a cell (Blue Rectangles) of table 500. FIG. 5N
illustrates an embodiment of inserting a reference to an entire row
of table 500 into the formula of host cell Blue Rectangles by
clicking on the header cell of that row. As depicted, a reference
to row Circles is inserted into the formula of host cell Blue
Rectangles by clicking on header cell Circles. Completion of the
formula being entered into host cell Blue Rectangles of table 500
is depicted in FIG. 5O, and the resulting value of cell Blue
Rectangles is depicted in FIG. 5P.
[0061] Address tabs may be available and/or displayed with a table.
In some embodiments, clicking on or otherwise selecting one or more
address tabs while in a formula editing mode results in reference
adding behavior similar to that described above with respect to
header cells. For example, in some embodiments, while in a formula
editing mode in a header row cell of a column of a table, clicking
on or otherwise selecting an address tab of another column results
in the insertion of a (header cell or address tab value) reference
to the selected column into the formula being entered into the host
cell. Likewise, in some embodiments, while in a formula editing
mode in a header column cell of a row of a table, clicking on or
otherwise selecting an address tab of another row results in the
insertion of a (header cell or address tab value) reference to the
selected row into the formula being entered into the host cell. In
some embodiments, while in a formula editing mode in a body or
footer cell, clicking on or otherwise selecting an address tab
results in the insertion into the formula of the host cell of a
(header cell or address tab value) reference to the row or column
associated with the address tab. In some such cases, the reference
to the row or column includes only the body cells of the row or
column. In some embodiments, when not in a formula editing mode,
clicking on or otherwise selecting an address tab results in the
selection of an entire associated column or row, including the body
cells, header cell, and/or footer cell associated with the column
or row.
[0062] FIG. 6 illustrates an embodiment of a process for inserting
references in a formula being entered into a host cell. In some
embodiments, process 600 is employed with respect to FIGS. 5C, 5G,
5K, and 5N to insert a reference into a formula. Process 600 starts
at 602 at which an indication that a formula is being entered into
a host cell is received. In some embodiments, the indication that a
formula is being entered is received at 602 in response to an
equals sign "=" being entered into the host cell. In various
embodiments, the host cell may be a body cell, a header cell, or a
footer cell. At 604, an indication of a selection of one or more
cells and/or address tabs is received. In various embodiments, the
selection may comprise a set of contiguous (i.e. a range) or a set
of non-contiguous cells. In various embodiments, the selected one
or more cells may comprise header cells, body cells, footer cells,
and/or address tabs. At 606, reference(s) to or associated with the
cell(s) and/or address tab(s) selected at 604 are inserted into the
formula being entered into the host cell, and process 600 ends. As
described above, in some embodiments, the reference adding behavior
of process 600 is based at least in part on the zones or types of
the host cell and/or the clicked cells. For example, in some
embodiments, in the cases in which the host cell comprises a header
row/column cell, selecting an other row/column header cell or
address tab results in the insertion of a (header cell or address
tab) reference to the selected row/column into the formula of the
host cell and selecting a body or footer cell results in the
insertion of a (header cell or address tab) reference to the
row/column associated with the selected body or footer cell into
the formula of the host cell. In the cases in which the host cell
comprises a body or footer cell, selecting a header cell or address
tab results in the insertion of a (header cell or address tab)
reference to the selected row/column into the formula of the host
cell while selecting an other body or footer cell results in the
insertion of a reference to the selected cell into the formula of
the host cell.
[0063] Although the foregoing embodiments have been described in
some detail for purposes of clarity of understanding, the invention
is not limited to the details provided. There are many alternative
ways of implementing the invention. The disclosed embodiments are
illustrative and not restrictive.
* * * * *