Comparing value in two different files

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

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" <Larry@discussions.microsoft.com> wrote in message news:D7662D04-FA44-48F8-8C8D-905C503A9D74@microsoft.com...
> 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
>

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

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" <Larry@discussions.microsoft.com> wrote in message news:D7662D04-FA44-48F8-8C8D-905C503A9D74@microsoft.com...
> > 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
> >
>
>
>