Hi,

This is probably a really easy thing to fix, but I am seriously struggling.

ISSUE AT HAND
What I need is to have a worksheet (call it totals) that contains values
from a multitude of other worksheets (call each of them company). This has
been working just fine. However, I realized that since the filenames change
every month (i.e. From 04-08workbook.xls to 05-08workbook.xls) I could make
it so I don't have to manually update the cell references:
in the "totals" workbook there are cells that refer to sums in each of the
"company" workbooks and these "company" workbooks are the ones that have the
aforementioned name changes.

MY FAILING SOLUTION
So, what I have tried to do was I got Excel to have a date cell formatted to
have {=now()} formatted to mm-yy and I tried to put in the information for
that date cell and file extension into the desination cells in the "Totals"
workbook with no avail. I have also been trying to write a macro to paste the
the date and file extension into a workbooks.open filename = _ paste
which has also not worked. Logically it seems that either method would work,
but I feel that the different properties of the open filename and the
destination cells are not compatible for what I think is logical.

Do you have any suggestions as to how I could get this to work? It would be
greatly appreciated

Try

=TEXT(NOW(), "mm-yy")

"Tony O" wrote:

Hi,

This is probably a really easy thing to fix, but I am seriously struggling.

ISSUE AT HAND
What I need is to have a worksheet (call it totals) that contains values
from a multitude of other worksheets (call each of them company). This has
been working just fine. However, I realized that since the filenames change
every month (i.e. From 04-08workbook.xls to 05-08workbook.xls) I could make
it so I don't have to manually update the cell references:
in the "totals" workbook there are cells that refer to sums in each of the
"company" workbooks and these "company" workbooks are the ones that have the
aforementioned name changes.

MY FAILING SOLUTION
So, what I have tried to do was I got Excel to have a date cell formatted to
have {=now()} formatted to mm-yy and I tried to put in the information for
that date cell and file extension into the desination cells in the "Totals"
workbook with no avail. I have also been trying to write a macro to paste the
the date and file extension into a workbooks.open filename = _ paste
which has also not worked. Logically it seems that either method would work,
but I feel that the different properties of the open filename and the
destination cells are not compatible for what I think is logical.

Do you have any suggestions as to how I could get this to work? It would be
greatly appreciated

I appreciate the feedback, but I have already done that in a cell. I am
trying to incorporate that =text(now(), "text") in the destination cells
whose filename extensions change from month to month.

Any ideas how I could get the destination cells to look at the destination.
I will show you what I have thus far:
=$N$1&" "&K2&"\"&L2&"\Claims Mgt\Open\2008\"&TEXT($M$1, "mm-yy")&" "&L2&"
Open Work Comp Loss Summary Analysis.xls"

this shows
F:\CompTrol Management\CompTrol Brody\AG Obrien\Claims Mgt\Open\2008\05-08
AG Obrien Open Work Comp Loss Summary Analysis.xls

I am trying to get it to look at a cell in that workbook, on sheet1
Any ideas?

Thank you
Tony O

"JLGWhiz" wrote:

Try

=TEXT(NOW(), "mm-yy")

"Tony O" wrote:

Hi,

This is probably a really easy thing to fix, but I am seriously struggling.

ISSUE AT HAND
What I need is to have a worksheet (call it totals) that contains values
from a multitude of other worksheets (call each of them company). This has
been working just fine. However, I realized that since the filenames change
every month (i.e. From 04-08workbook.xls to 05-08workbook.xls) I could make
it so I don't have to manually update the cell references:
in the "totals" workbook there are cells that refer to sums in each of the
"company" workbooks and these "company" workbooks are the ones that have the
aforementioned name changes.

MY FAILING SOLUTION
So, what I have tried to do was I got Excel to have a date cell formatted to
have {=now()} formatted to mm-yy and I tried to put in the information for
that date cell and file extension into the desination cells in the "Totals"
workbook with no avail. I have also been trying to write a macro to paste the
the date and file extension into a workbooks.open filename = _ paste
which has also not worked. Logically it seems that either method would work,
but I feel that the different properties of the open filename and the
destination cells are not compatible for what I think is logical.

Do you have any suggestions as to how I could get this to work? It would be
greatly appreciated

Do you mean like
(beginning of formula)&TEXT(Sheet2!$M$1, "mm-yy")& (rest of formula)
to reference a cell on sheet 2 from sheet 1?

"Tony O" wrote:

I appreciate the feedback, but I have already done that in a cell. I am
trying to incorporate that =text(now(), "text") in the destination cells
whose filename extensions change from month to month.

Any ideas how I could get the destination cells to look at the destination.
I will show you what I have thus far:
=$N$1&" "&K2&"\"&L2&"\Claims Mgt\Open\2008\"&TEXT($M$1, "mm-yy")&" "&L2&"
Open Work Comp Loss Summary Analysis.xls"

this shows
F:\CompTrol Management\CompTrol Brody\AG Obrien\Claims Mgt\Open\2008\05-08
AG Obrien Open Work Comp Loss Summary Analysis.xls

I am trying to get it to look at a cell in that workbook, on sheet1
Any ideas?

Thank you
Tony O

"JLGWhiz" wrote:

Try

=TEXT(NOW(), "mm-yy")

"Tony O" wrote:

Hi,

This is probably a really easy thing to fix, but I am seriously struggling.

ISSUE AT HAND
What I need is to have a worksheet (call it totals) that contains values
from a multitude of other worksheets (call each of them company). This has
been working just fine. However, I realized that since the filenames change
every month (i.e. From 04-08workbook.xls to 05-08workbook.xls) I could make
it so I don't have to manually update the cell references:
in the "totals" workbook there are cells that refer to sums in each of the
"company" workbooks and these "company" workbooks are the ones that have the
aforementioned name changes.

MY FAILING SOLUTION
So, what I have tried to do was I got Excel to have a date cell formatted to
have {=now()} formatted to mm-yy and I tried to put in the information for
that date cell and file extension into the desination cells in the "Totals"
workbook with no avail. I have also been trying to write a macro to paste the
the date and file extension into a workbooks.open filename = _ paste
which has also not worked. Logically it seems that either method would work,
but I feel that the different properties of the open filename and the
destination cells are not compatible for what I think is logical.

Do you have any suggestions as to how I could get this to work? It would be
greatly appreciated

I am not sure what you mean by destination cells. The cell with the formula
is the destination cell in this case. The other cells that you reference are
source cells or precedents.




"Tony O" wrote:

I appreciate the feedback, but I have already done that in a cell. I am
trying to incorporate that =text(now(), "text") in the destination cells
whose filename extensions change from month to month.

Any ideas how I could get the destination cells to look at the destination.
I will show you what I have thus far:
=$N$1&" "&K2&"\"&L2&"\Claims Mgt\Open\2008\"&TEXT($M$1, "mm-yy")&" "&L2&"
Open Work Comp Loss Summary Analysis.xls"

this shows
F:\CompTrol Management\CompTrol Brody\AG Obrien\Claims Mgt\Open\2008\05-08
AG Obrien Open Work Comp Loss Summary Analysis.xls

I am trying to get it to look at a cell in that workbook, on sheet1
Any ideas?

Thank you
Tony O

"JLGWhiz" wrote:

Try

=TEXT(NOW(), "mm-yy")

"Tony O" wrote:

Hi,

This is probably a really easy thing to fix, but I am seriously struggling.

ISSUE AT HAND
What I need is to have a worksheet (call it totals) that contains values
from a multitude of other worksheets (call each of them company). This has
been working just fine. However, I realized that since the filenames change
every month (i.e. From 04-08workbook.xls to 05-08workbook.xls) I could make
it so I don't have to manually update the cell references:
in the "totals" workbook there are cells that refer to sums in each of the
"company" workbooks and these "company" workbooks are the ones that have the
aforementioned name changes.

MY FAILING SOLUTION
So, what I have tried to do was I got Excel to have a date cell formatted to
have {=now()} formatted to mm-yy and I tried to put in the information for
that date cell and file extension into the desination cells in the "Totals"
workbook with no avail. I have also been trying to write a macro to paste the
the date and file extension into a workbooks.open filename = _ paste
which has also not worked. Logically it seems that either method would work,
but I feel that the different properties of the open filename and the
destination cells are not compatible for what I think is logical.

Do you have any suggestions as to how I could get this to work? It would be
greatly appreciated