Variable filename

[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

[Cheeky Charlie]

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

You can use find & replace on formulae...

[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

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

[Cheeky Charlie]

No worries,

Just open find&replace dialogue (ctrl+h), open the options and elect to look in formulas, simply find and replace exactly as you've described.

The key thing is the drop down for look in formulae, not values

[mgreenop]

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

mgreenop