Saving data

L

legepe

Hi all,
I need to save data (results) from a base spread sheet program that i use on
a weekly basis. i am in the middle of building this program, and have just
discovered macros, but this, along with links is about my current knowledge
of excel
how can i automate to accumulate data from the base spread sheet (program)
when i clear all data from the program to produce fresh results the following
week, and to keep past data up to date and available for further use.
Any help would be appreciated.
legepe
 
P

Paul Corrado

With a combination of formulas and dynamic named ranges, it is possible to
just add the new data to a single table and program the final report to pick
up the relevant information as it is added to the data table. This approach
however can be limited based on the amount of information that you are
adding each period, and the processing capacity and memory of your computer.

Without much more information though it is difficult to provide a definitive
solution.

However, lets say that you have a simple data table consisting of three rows
and three columns in the range A1:C4

Name Month Sales
Jim January 55.00
Mary January 100.00
Pete February 75.00

The Formula

=SUMPRODUCT((A2:A4="Jim")*(B2:B4="February")*(C2:C4))

Would return a vale of 0.00 as Jim had no sales in January. The Sumproduct
formula was able to determine this using the two criteria, Jim and January.
More criteria arguments could easily be inserted to suit the needs of a more
complex data configuration and reporting capability.

Dynamic Named Ranges can be created using the OFFSET function and they will
adjust as more records are added to your data table.

Using Insert/Name/Define and the formula

=OFFSET(A1,1,0,COUNTA(A:A)-1)

We could create a dynamic named range called "Name" that will expand to
include all the entries in column A as the data table changes.

Similarly, we could create a named range called Month for column B using the
formula

=OFFSET(B1,1,0,COUNTA(A:A)-1)

Note that in this case I have still referred to the number of entries in
column A. This will ensure that the length of the both named ranges is
identical. If it is not, the SUMPRODUCT formula will produce an error.


Substituting these named ranges in the first formula we get

=SUMPRODUCT((Name="Jim")*(Month="February")*(C2:C4)) note there are not
""'s around the named ranges

I know this is probably not exactly what you were looking for, but hopefully
there may be something in there you will find that is useful.

HTH

PC
 
Top