Format Cell contents as MM
 

Hello
I have a monthly file My File mm-yyyy.xls. I am trying to reference last
months file using cell C1 (Named month) and indirect.ext using
cell("FILENAME'). The month portion of the formula is evaluating to 01 no
matter the month.
My formula:
=INDIRECT.EXT("'"&LEFT(CELL("FILENAME",A1),FIND("-",CELL("FILENAME",A1))-3)&TEXT((MO-1),"MM")&"-"&YR&".xls]Monthly Summary'!$"&"b$37")

What should I change in TEXT((MO-1) so it will evaluate to the previous
month. I realize that January will be a problem and plan on manually
entering the values unless some one has a suggestion for that too.

Thanks!!!

Format Cell contents as MM
 

This part

MO-1


needs to be a date, is it?

If it is a month index number (which it looks like since you are subtracting
1) it will always be January since
0-31 as date serial is Jan 1900?

Try to change the text function part to


=TEXT(DATE(2008,MO-1,1),"MM")


You can basically put in whatever year you want where I put 2008, it is just
to get a legit date
so you can subtract one month if that's what you are doing

--


Regards,


Peo Sjoblom

"Sandy" wrote in message
...
Hello
I have a monthly file My File mm-yyyy.xls. I am trying to reference last
months file using cell C1 (Named month) and indirect.ext using
cell("FILENAME'). The month portion of the formula is evaluating to 01 no
matter the month.
My formula:
=INDIRECT.EXT("'"&LEFT(CELL("FILENAME",A1),FIND("-",CELL("FILENAME",A1))-3)&TEXT((MO-1),"MM")&"-"&YR&".xls]Monthly
Summary'!$"&"b$37")

What should I change in TEXT((MO-1) so it will evaluate to the previous
month. I realize that January will be a problem and plan on manually
entering the values unless some one has a suggestion for that too.

Thanks!!!

Format Cell contents as MM
 

If you use an integer it works for me. Interestingly if you use "dd" instead
of "MM" it seems to work, i'm not sure why. The only isue i had when doing it
that way was a date in january returned 00.
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"Sandy" wrote:

Hello
I have a monthly file My File mm-yyyy.xls. I am trying to reference last
months file using cell C1 (Named month) and indirect.ext using
cell("FILENAME'). The month portion of the formula is evaluating to 01 no
matter the month.
My formula:
=INDIRECT.EXT("'"&LEFT(CELL("FILENAME",A1),FIND("-",CELL("FILENAME",A1))-3)&TEXT((MO-1),"MM")&"-"&YR&".xls]Monthly Summary'!$"&"b$37")

What should I change in TEXT((MO-1) so it will evaluate to the previous
month. I realize that January will be a problem and plan on manually
entering the values unless some one has a suggestion for that too.

Thanks!!!

Format Cell contents as MM
 

I'd put today's date in that named cell instead:
say A1 contained a formula like: =today()

Then I'd use:

=INDIRECT.EXT("'"&LEFT(CELL("FILENAME",A1),
FIND("-",CELL("FILENAME",A1))-3)
&TEXT(a1-day(a1),"mm-yyyy")&".xls]Monthly Summary'!b37")

I removed the $ from inside the text and joined the strings together, too.

and
=a1-day(a)
will return the last day of the previous month of the date in A1.


Sandy wrote:

Hello
I have a monthly file My File mm-yyyy.xls. I am trying to reference last
months file using cell C1 (Named month) and indirect.ext using
cell("FILENAME'). The month portion of the formula is evaluating to 01 no
matter the month.
My formula:
=INDIRECT.EXT("'"&LEFT(CELL("FILENAME",A1),FIND("-",CELL("FILENAME",A1))-3)&TEXT((MO-1),"MM")&"-"&YR&".xls]Monthly Summary'!$"&"b$37")

What should I change in TEXT((MO-1) so it will evaluate to the previous
month. I realize that January will be a problem and plan on manually
entering the values unless some one has a suggestion for that too.

Thanks!!!

--

Dave Peterson

Format Cell contents as MM
 

Take a look at this formula, it takes a date in a1 then subtracts the the
number of days in the month before to return the month number of the month
before. Since it uses the days in the previous month, it can never go back
two months if that makes since.
=TEXT(A1-(TEXT(EOMONTH(A1,1),"dd")),"mm")

--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"Sandy" wrote:

Hello
I have a monthly file My File mm-yyyy.xls. I am trying to reference last
months file using cell C1 (Named month) and indirect.ext using
cell("FILENAME'). The month portion of the formula is evaluating to 01 no
matter the month.
My formula:
=INDIRECT.EXT("'"&LEFT(CELL("FILENAME",A1),FIND("-",CELL("FILENAME",A1))-3)&TEXT((MO-1),"MM")&"-"&YR&".xls]Monthly Summary'!$"&"b$37")

What should I change in TEXT((MO-1) so it will evaluate to the previous
month. I realize that January will be a problem and plan on manually
entering the values unless some one has a suggestion for that too.

Thanks!!!