Excel External references

[cchristy]

Hi,

I am trying to create a formula that will update itself based on the month.

What i am doing is extracting data from a spreadsheet based on the current month.

i.e. In July, the formula will do a HLOOKUP on a spreadsheet called 2011-07.xlsx. In August, i would like this to point to 2011-08.xslx.

As i am using the reference >100 times, i would like to avoid having to update them all individually and was wondering if part of the file name can be read from a cell?

If not, is there any easier ways to doig this. I am not particularly fond of doing multiple if statements as enentually, i will need to redo the sheet again after several months.

 

[GlennUK]

What do you mean, "update them all individually"? If you click the Office Icon, choose Prepare / Edit Links, you point to the current file ( 2011-07.xlsx ), click the Change Source button, select the new file ( 2011-08.xslx ), and that's it, surely?

 

[kidwispa]

Not sure of a way to change the formulas as the month changes but could you not just highlight the column the formulas are located in and do a find and replace on the particular area of the formula you wish to change (ie change "- 07.xls" to "- 08.xls")? This would change all of your formulas in a matter of seconds...

 

[cchristy]

What do you mean, "update them all individually"? If you click the Office Icon, choose Prepare / Edit Links, you point to the current file ( 2011-07.xlsx ), click the Change Source button, select the new file ( 2011-08.xslx ), and that's it, surely?

Didnt think you could do that.

That works. Thanks for your help.

 

[GlennUK]

That's great!