Named Ranges

[Sharkie21]

I have formulas that reference a certain cell e.g.

Cell C1: Formula = B1 + 4
Cell C3: Formula = B1 + 10

Different Sheet:
Cell A1: Formula = Sheet1!B1 + 6

I changed B1 to be a named range called "base_charge".

I want my formulas to update to pick up the name range instead. I don't want to use a find and replace since I have a ton of formulas and it is very slow.

So I want: Cell C1: Formula = base_charge + 4
etc

Thanks.

 

[Sharkie21]

So I sorta figured it out... you can go to insert-> name -> apply. However that only works for the current sheet, how do you make it apply to all sheets?

 

[Marcelo Branco]

So I sorta figured it out... you can go to insert-> name -> apply. However that only works for the current sheet, how do you make it apply to all sheets?

Try setting the Scope of the named-range to Workbook

HTH

M.

 

[Sharkie21]

Try setting the Scope of the named-range to Workbook

HTH

M.

what do you mean by that? Or how do I do that?

Thanks.

 

[Marcelo Branco]

Go to Names Manager select your named-range (base_charge) and click Edit

Check if the box Scope is set to Workbook.

M.

 

[Sharkie21]

Go to Names Manager select your named-range (base_charge) and click Edit

Check if the box Scope is set to Workbook.

M.

Is that some sort of add in?

 

[Marcelo Branco]

Is that some sort of add in?

No
What excel-version are you using?

M.

 

[Sharkie21]

no
what excel-version are you using?

M.

2003

 

[Marcelo Branco]

I tested in Excel 2007 and 2010

Not sure if in 2003 you can use a named-range in a different sheet.

M.

 

[Marcelo Branco]

I think you can. See this

http://www.cpearson.com/excel/DefinedNames.aspx

and look at Global-Scope And Sheet-Scope Names

Insert > Name > Define

and in Refers To simply
=$B$1

not =Sheet1!$B$1

M.