1) The number of rows for data can vary from day to day depending on how many
tests are being run. Also, the sample number can be in different rows on
different days. I was planning on using vlookup to get the value from the
file two days ago, therefore, the row number doesn't matter. But for vlookup
to work I need the file name in the vlookup formula to change automatically.
2) The data is always entered on the same day as the the file name.
3) Normally, the month names are abbreviated. But I can have the employees
always use three or four letters, i.e., March will be Mar, July wil be Jul,
etc.


"JPW" wrote:

You would need to write some VBA code to accomplish this. We need specifics on 1) which rows contain data in column B? Is the number
of rows always the same, and is this the ONLY data on this sheet? 2) is your data always entered on the same clock date as the file
will be named? That is, would data for June 19th always be entered on the 19th, or would it sometimes be entered on the 20th, etc.
3) are the month names always spelled out completely in filenames?

"Larry" wrote in message ...
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