nilpo,

You can use the mid() function to brake apart your
workbook name. There is an explanation in VBA Help. It
takes 3 arguements, the variable you want to look at,
what byte to start on, how many bytes to return.
strg1 = mid(ActiveWork2book.name,1,2)
will look at the name of the active workbook, then start
at byte1 and put two bytes into the variable strg1. If
the active book is 0303.xls then strg1 = 03.

Once we know the starting month, we can build a small
routine using SELECT CASE. This is in the help also.

Select Case strg1
case 03
open 0203.xls you'll need to insert the exact code
open 0103.xls
case 06
etc.
end select

That should get you started. Good luck
Mike


-----Original Message-----
I am creating workbook for basic cash control. I have a
workbook for
each month. In each workbook I have the following
sheets: Week1,
Week2, Week3, Week4, Week5, Monthly, Quarterly, YTD.

I have configured the workbook so that the Monthly sheet
updates from
each of the weeks for that month. Now I want to enter
the formulas for
the Quartely and YTD sheets. I use a simple naming
procedure for the
workbooks.

2 digit year, 2 digit month

So January 2003 would be 0301.xls

I know how to reference the outside sheets, but I get
the usual #REF
error unless I create empty workbooks for each month
ahead of time.

I want to configure a macro or some means of checking to
see if the
individual files exist, then returning the appropriate
string to use
for the formulas. Then I will adjust the cell
references as need.

Each sheet of each book is configured exactly the same.
I have a
column for each day of the week, and a weekly total.
The monthly adds
the corresponding columns from each week. So the
monthly adds all the
Mondays, etc. and finally the Weekly Total column adds
each of the
Weekly totals from first five sheets.

I want to reference these montly totals for my Quartely
and YTD sheets.
In other words I need to return the value for the
function and insert
it into each cell. So that the YTD references for
0303.xls would look
something like the following:

For cell P1,
=SUM(Monthly!P1,[0302.xls]Monthly!P1,[0301.xls]Monthly!
P1)

For cell B6,
=SUM(Monthly!B6,[0302.xls]Monthly!B6,[0301.xls]Monthly!
B6)

I need to insert function like the above into each cell
depending on
what cell it's being referenced from.

The quarterly should operate exactly the same except
that it doesn't
start from January but rather from the start of the
quarter.

First Quarter = January to March
Second Quarter = April to June
Third Quarter = July to September
Fourth Quarter = October to December.

I will be saving the workbook as a template and renaming
it for each
month as described above. I know there has to be a way
to take the
current workbook's filename and find the two digit
month, check to see
if each previous month exists, and return the values
accordingly.

I want to use it something like the following if
possible, unless there
is a better way:

getQuarterly(B6)
getYTD(B6)

to return the examples above.



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from
http://www.ExcelForum.com/

.