[Joe Bannister]

Hello, can anyone help?

I have a workbook named 16_04_abc.xls where 16 is the
week number and 04 the year number.
It is a rolling sheet and so when week 16 is complete a
macro attached to a button prepares week 17 for data
input etc, until this point 17_04_abc.xls does not exist.
The workbook has 2 sheets.

Now alot of the info on in the workbook sheet1 needs to
be transfered to another workbook. The other workbook
details months.

I know a cell in the other workbook can say
=C:\[16_04_abc.xls]Sheet1$A$1 but what about references
to files that don't exist yet. I get #Ref errors.

So, i would like to have the user input a row number and
a week number and to have a macro input into the relevant
cell the formula to pull in the figure from the first
workbook.
I can get it to display the correct formula in the cell
but this is as text not a formula.

Can anyone help solve my problem?

Cheers

Joe

[jaf]

Hi Joe,
When do you create the link?
Excel has an EoMonth function to which you can compare today's date.

I suggest a workbook_open macro that will fire 1 week before the end of the
current month,
and automatically create the new workbook, copy data, formulas, links etc.

An Excel template (.xlt) would save a lot of work.



--

John

johnf202 at hotmail dot com


"Joe Bannister" wrote in message
...
Hello, can anyone help?

I have a workbook named 16_04_abc.xls where 16 is the
week number and 04 the year number.
It is a rolling sheet and so when week 16 is complete a
macro attached to a button prepares week 17 for data
input etc, until this point 17_04_abc.xls does not exist.
The workbook has 2 sheets.

Now alot of the info on in the workbook sheet1 needs to
be transfered to another workbook. The other workbook
details months.

I know a cell in the other workbook can say
=C:\[16_04_abc.xls]Sheet1$A$1 but what about references
to files that don't exist yet. I get #Ref errors.

So, i would like to have the user input a row number and
a week number and to have a macro input into the relevant
cell the formula to pull in the figure from the first
workbook.
I can get it to display the correct formula in the cell
but this is as text not a formula.

Can anyone help solve my problem?

Cheers

Joe