I import data from Lotus Notes into an Excel 2007 table through a link. I use
many COUNTIF statements to give me a count of records with certain criteria.
I want to copy the count results into another worksheet to track that days
results. The next day, I refresh the data and it calculates new counts. I now
want to take a snapshot of that days counts and log them in the worksheet.
This will happen day after day so that I can chart a trend. My problem is, I
can't figure out how to make a running log of the counts day after day, when
I refresh the same data table each day. I want to automate the log.

Anyone ever done this before and can give me some hints.

Assuming a worksheet with a list of dates (Jan 1 through Dec 31) in A1
through A365. Column B contains the following formula:

IF(A1=TODAY(),data_cell_reference,""), but if I do this, the previous day's
calculation will blank out the data. I want the data from the previous days
snapshot to remain. Other attempts I've tried create a circular reference.
--
TClawson
Project Management

This may not be the most efficient way to do this but here goes...

You can create a macro where you copy your COUNTIF values and do a "Paste
Special" "Values" only to another sheet in your workbook (or where ever you
choose). You would need to run the macro daily or work it in with an
exsisting macro.

Hope this helps.

"TClawson" wrote:

I import data from Lotus Notes into an Excel 2007 table through a link. I use
many COUNTIF statements to give me a count of records with certain criteria.
I want to copy the count results into another worksheet to track that days
results. The next day, I refresh the data and it calculates new counts. I now
want to take a snapshot of that days counts and log them in the worksheet.
This will happen day after day so that I can chart a trend. My problem is, I
can't figure out how to make a running log of the counts day after day, when
I refresh the same data table each day. I want to automate the log.

Anyone ever done this before and can give me some hints.

Assuming a worksheet with a list of dates (Jan 1 through Dec 31) in A1
through A365. Column B contains the following formula:

IF(A1=TODAY(),data_cell_reference,""), but if I do this, the previous day's
calculation will blank out the data. I want the data from the previous days
snapshot to remain. Other attempts I've tried create a circular reference.
--
TClawson
Project Management