Larry wrote:
as they take pH measurements from hundreds of tests. I have a spreadsheet
template that multiple employees enter data every other day. Once the data
is entered the template is saved as an xls file and the name of the file
includes the date the data was entered. What I would like is another column
so that when specific data is entered it is compared to value entered two
days previous so that the employees can tell if the current value is
approximately the same, within a certain range. As a simplied example shown
below, I have six samples shown in column A and the pH value is entered in
column B, and the file is saved as Data_19_June_05.xls. As the employee's
enter data for June 21 (file name Data_21_June_05.xls) I need them to know
if the value is plus or minus 0.1 from the value on June 19. So for Sample
1a the pH value measured on June 21 and typed into the spreadsheet should be
2.5 to 2.7 and 2.8 to 3.0 for Sample 1b. I can easily compare data from one
day to the next using links, but the problem is these would be hard links,
which means when entering data on June 23 (file name Data_23_June_05.xls) the
links would still compare to the data from June 19. The template needs to
change the links automatically to the file from two days ago. Can this be
done with formulas or can a macro accomplish this?

Example data in file Data_19_June_05.xls:

Sample pH
1a 2.6
1b 2.9
1c 3.4
2a 2.8
2b 3.1
2c 3.5

-----------------

If I understand you correctly, it sounds like you know how to compare from one
file to another, and your problem is that the file name keeps changing.

The low tech solution is to have the person enter into a cell at the top of the
page the name of the previous file. You can then pick that text up with an
INDIRECT() statement and get the data you need from the other sheet.

Given the structure of your file name, the high tech solution would be to do the
same thing, but automagically create the file name in the cell. Something of
the form:

="Data_"&TEXT(TODAY()-2,"dd")&"_"&TEXT(TODAY()-2,"mmmm")&"_"&TEXT(TODAY()-2,"yy")

This would rely on your naming conventions being rigorously followed and would
get into trouble if people skipped a day of data, or wanted to compare to some
older data or some such.

Basically you can make this problem as "simple and efficient" or as "complex and
wonderful" as you want. Personally, I'd just ask the people to type in the name
of the prior file before they begin taking data.

Good luck...

Bill