Hello -

I'm designing a forecast package that will be (hopefully) used for some
years to come. I'd like to build the pkg to be 'updateable' with
little effort or technical knowledge, but not sure how to proceed.
Here's what I'd like to do...

Every month, the pkg is updated and saved to a specific folder (ie
'FY06'). This folder will be used all year (so a 'constant'), but will
change next September (to 'FY07'). This pkg will be used by over 30
stores, so I need a way to update the path to the new folder without
having to personally go into each store's pkg and update the vba code
myself.

I was thinking of utilizing a userform (accessed via a button) to
collect the updated info and somehow 'pass' it to where it needs to go.
Can this be done? If so, where should I put the 'variable constants'
within the structure of the Code?

TIA!

ray

Why not store the files in a folder named CurrentFY?
Then the folder won't have to change when the year changes....you'll only
need to archive PriorFY files.

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Ray" wrote:

Hello -

I'm designing a forecast package that will be (hopefully) used for some
years to come. I'd like to build the pkg to be 'updateable' with
little effort or technical knowledge, but not sure how to proceed.
Here's what I'd like to do...

Every month, the pkg is updated and saved to a specific folder (ie
'FY06'). This folder will be used all year (so a 'constant'), but will
change next September (to 'FY07'). This pkg will be used by over 30
stores, so I need a way to update the path to the new folder without
having to personally go into each store's pkg and update the vba code
myself.

I was thinking of utilizing a userform (accessed via a button) to
collect the updated info and somehow 'pass' it to where it needs to go.
Can this be done? If so, where should I put the 'variable constants'
within the structure of the Code?

TIA!

ray

Thanks for the response -- 2 thoughts on this:
** this would definitely work, except that 'upper mgmt' won't like
it....
** the FY example is just one of many (6-8) semi-variable constants
that I want to setup

Any other ideas?


Ron Coderre wrote:
Why not store the files in a folder named CurrentFY?
Then the folder won't have to change when the year changes....you'll only
need to archive PriorFY files.

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Ray" wrote:

Hello -

I'm designing a forecast package that will be (hopefully) used for some
years to come. I'd like to build the pkg to be 'updateable' with
little effort or technical knowledge, but not sure how to proceed.
Here's what I'd like to do...

Every month, the pkg is updated and saved to a specific folder (ie
'FY06'). This folder will be used all year (so a 'constant'), but will
change next September (to 'FY07'). This pkg will be used by over 30
stores, so I need a way to update the path to the new folder without
having to personally go into each store's pkg and update the vba code
myself.

I was thinking of utilizing a userform (accessed via a button) to
collect the updated info and somehow 'pass' it to where it needs to go.
Can this be done? If so, where should I put the 'variable constants'
within the structure of the Code?

TIA!

ray

I think the answer to my question is to set up a UserForm to collect
all of my semi-constant variables -- the administrator could click a
button to call-up the userform, enter any updates, and this would
update the 'constants' until the next time the button is pushed.

BUT, how do I set this up? I know how to build the UserForm, but where
do I go from here? Where is the data stored and how would I reference
these fields in other modules/subs?


Ray wrote:
Thanks for the response -- 2 thoughts on this:
** this would definitely work, except that 'upper mgmt' won't like
it....
** the FY example is just one of many (6-8) semi-variable constants
that I want to setup

Any other ideas?


Ron Coderre wrote:
Why not store the files in a folder named CurrentFY?
Then the folder won't have to change when the year changes....you'll only
need to archive PriorFY files.

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Ray" wrote:

Hello -

I'm designing a forecast package that will be (hopefully) used for some
years to come. I'd like to build the pkg to be 'updateable' with
little effort or technical knowledge, but not sure how to proceed.
Here's what I'd like to do...

Every month, the pkg is updated and saved to a specific folder (ie
'FY06'). This folder will be used all year (so a 'constant'), but will
change next September (to 'FY07'). This pkg will be used by over 30
stores, so I need a way to update the path to the new folder without
having to personally go into each store's pkg and update the vba code
myself.

I was thinking of utilizing a userform (accessed via a button) to
collect the updated info and somehow 'pass' it to where it needs to go.
Can this be done? If so, where should I put the 'variable constants'
within the structure of the Code?

TIA!

ray

Sounds like a perfect situation for Google Spreadsheets to me!
Just put "google spreadsheets" into a google search.

With scant details, I'm just guessing at what you're trying to do here...

My experience with most managers is that they prefer to go to a single
source for ALL of their data, so....
Perhaps you could publish a Table of Contents workbook in a generic folder
called Financial Reports. The TOC file would contain hyperlinks to fiscal
year workbooks. Users select a year from a dropdown list and the hyperlinks
adjust accordingly.

Use the HYPERLINK() function...
Example:
A1: (contains a Data Validation dropdown with 2005, 2006, 2007)

C3:
=HYPERLINK("\\server\mainfolder\"&LOOKUP($A$1,{200 5,2006,2007},{"FY05","FY06","FY07"})&"\IncStmt.xls ","Income Statement")

If 2006 is selected in A1....
Then B3 references: "\\server\mainfolder\FY06\IncStmt.xls"
Clicking on that link would open that workbook.

(Of course, you could drive the file locations from a lookup list elsewhere
in the workbook.)

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Ray" wrote:

Thanks for the response -- 2 thoughts on this:
** this would definitely work, except that 'upper mgmt' won't like
it....
** the FY example is just one of many (6-8) semi-variable constants
that I want to setup

Any other ideas?


Ron Coderre wrote:
Why not store the files in a folder named CurrentFY?
Then the folder won't have to change when the year changes....you'll only
need to archive PriorFY files.

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Ray" wrote:

Hello -

I'm designing a forecast package that will be (hopefully) used for some
years to come. I'd like to build the pkg to be 'updateable' with
little effort or technical knowledge, but not sure how to proceed.
Here's what I'd like to do...

Every month, the pkg is updated and saved to a specific folder (ie
'FY06'). This folder will be used all year (so a 'constant'), but will
change next September (to 'FY07'). This pkg will be used by over 30
stores, so I need a way to update the path to the new folder without
having to personally go into each store's pkg and update the vba code
myself.

I was thinking of utilizing a userform (accessed via a button) to
collect the updated info and somehow 'pass' it to where it needs to go.
Can this be done? If so, where should I put the 'variable constants'
within the structure of the Code?

TIA!

ray

Hi Martin -

Not sure how this solves my issue....can you clarify a bit you thinking
on this?

TIA, ray


MartinW wrote:
Sounds like a perfect situation for Google Spreadsheets to me!
Just put "google spreadsheets" into a google search.