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/