Variable filename

Hi everyone
This is my first venture into the forum.
I have several excel files that I am trying to summarise on a seperate workbook. I can do all this fine, but not very efficiently. The following formula is an example of what I am trying to do:

=IF(AND('[2010 - 06.xls]Expenditure'!$G$2=0,'Fixed costs'!$B4=""),"",
IF('[2010 - 06.xls]Expenditure'!$G$2=0,'Fixed costs'!$B4,'[2010 - 06.xls]Expenditure'!$G$2))

The formula works fine, however I am repeating the formula in the spreadsheet 533 times. I want to be able to change the filename automatically - '2010 - 06.xls' bit without having to change each formula individually. I will also want to change it all again in subsequent years.
Has anybody got any great ideas, my VB skills are very limited. Thanks in anticipation.

mgreenop

Originally Posted by mgreenop

Hi everyone
This is my first venture into the forum.
I have several excel files that I am trying to summarise on a seperate workbook. I can do all this fine, but not very efficiently. The following formula is an example of what I am trying to do:

=IF(AND('[2010 - 06.xls]Expenditure'!$G$2=0,'Fixed costs'!$B4=""),"",
IF('[2010 - 06.xls]Expenditure'!$G$2=0,'Fixed costs'!$B4,'[2010 - 06.xls]Expenditure'!$G$2))

The formula works fine, however I am repeating the formula in the spreadsheet 533 times. I want to be able to change the filename automatically - '2010 - 06.xls' bit without having to change each formula individually. I will also want to change it all again in subsequent years.
Has anybody got any great ideas, my VB skills are very limited. Thanks in anticipation.

mgreenop

Originally Posted by Cheeky Charlie

What do you want to change to what (specifically)?

You can use find & replace on formulae...

Durrr... Yes sorry, I want to change the '2010 - 06.xls part of the filename to another date such as 2010 - 07.xls. This appears 3 times in the formula and I have multiple entries per month and obviously 12 months to modify. I hope this helps

Fantastic, thanks.
I don't know why I didn't think of that.

mgreenop