Named ranges - why do have to keep confirming location?

[stuartmacdonald]

I have a spreadsheet which uses 16 named ranges to identify 16 individual workbooks, however every time I copy/change a formula that uses them, I have to re confirm the file location! Any idea why, given the named range contains the file location!

I have put together the formula below:

=[Adrian]Jul!$H$46+[Francis]Jul!$H$46+[Gareth]Jul!$H$46+[Ian]Jul!$H$46+[James]Jul!$H$46+[Jessica]Jul!$H$46+[Jess]Jul!$H$46+[Jon]Jul!$H$46+[Matthew]Jul!$H$46+[Natalie]Jul!$H$46+[Paul]Jul!$H$46+[Richard]Jul!$H$46+[Rupert]Jul!$H$46+[Sarah]Jul!$H$46+[Stuart]Jul!$H$46+[Vic]Jul!$H$46

Then when I copy the formula to the next cell and use find/replace to change the formula to Aug'!$H$46 it forces me to locate every individual file on my server.

My named ranges are set up like this:

="H:\CERN\Timesheets\2009-2010\[Adrian Nolan 2009-2010.xls]"

 

[Andrew Poulsom]

You should stick with your original thread:

http://www.mrexcel.com/forum/showthread.php?t=392391

The name's reference should include the cell address and shouldn't be a string (no double quotes).

 

[stuartmacdonald]

Sorry, new to this

I wanted to use the named ranges across a whole range of cells, which is why I left off the cell address. The double quotes seem to get inserted automatically when I create the named range!

I want to use these named ranges with at least a dozen different cell references, across each month of the year - so its not really going to work using a named range with the cell ref.

Any ideas?

 

[Andrew Poulsom]

Sorry, no ideas. The name must refer to a range. Having your data on separate worksheets complicates the issue.