Change range name location for successive sheets

Hi davter,

Welcome to the forum and have a wonderful year ahead.

Not sure how the formulas and names have been setup in the workbook but I believe a name define in any sheet can be used throughout the workbook if the scope is extended to "workbook" [ Excel 2007 onwards] and also in previous version (Excel 2003 and previous ver). If you want to isolate the year in the sheets 2011 and 2012, then you might need to define names exclusively for both sheets - where scope should not be for whole workbook. Let me know if I did not understand your issue correctly.



Regards,
DILIPandey

<click on below 'star' if this helps>

Open the formula file and Press Alt+E+K and click CHANGE SOURCE and locate the file.

---

Sixthsense

Originally Posted by davter

How can I change part of a formula or a range name used in 2 separate sheets?.
Details are: I use a named range in a Sheet - "Year" this is refered to by many formula strings in a "2011" sheet, Now I have a new sheet in the same workbook for "2012"
How can I break the link from the 2011 "Year" and the 2012 "Year", if I cannot isolate the name range year in these two sheets, then I have to manually change the range name in the new sheet in hundreds of locations. The formula itself is similar to the following: SUMIF(paymentDate,">="&DATE(year,1,1),amount)-(SUMIF(paymentDate,">="&DATE(year,2,1),amount))
I would like the "year" of each sheet to refer to a fixed cell on that sheet and on that sheet only.
Dave