Microsoft Office Tutorials and References
In Depth Information
Method 2: Spreadsheet Databases
This is the next logical step above manual spreadsheet reporting.
Rather than manually massaging the same historical data each
month, it makes sense to massage each month’s data once, save
the transformed data in a spreadsheet database, and then use
those results for subsequent reports.
An INDEX-MATCH formula combination is typically used to retrieve
data from a spreadsheet database. To illustrate, suppose a
spreadsheet database contains the GL trial balance for several
months. Suppose column A contains the Account number; column
B contains the Description; and column C contains the Current
month’s data, retrieved by formula from subsequent columns of
monthly data.
The formula needed to return the description for account “1234”
would be…
=INDEX(Description, MATCH(“1234”,Account,0))
…and the formula to return the current amount would be…
=INDEX(Current, MATCH(“1234”,Account,0))
Because Excel formulas pull data from the spreadsheet database,
this uses the pull approach to populate spreadsheet cells.
The Advantages
Using spreadsheet databases can save a great deal of time when
compared to the build-from-scratch method. This is because users
need to massage the data only once, and then they store it in the
spreadsheet database.
Also, this is a very flexible approach, because spreadsheet
databases can contain data from any source, internal or external.
Search JabSto ::

Custom Search