Creating a Dynamic Function

[nilpo]

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.

 

[Mike Waldron]

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.



------------------------------------------------
[/url]
~~ View and post usenet messages directly from http://www.ExcelForum.com/

.