Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Named Cell Ranges
I have a number of workbooks that look at a variables workbook which has a
number of named cell ranges. I have added new lines to a particular named area and now some of the formulae in the other workbooks don't work. I'm assuming it's to do with the change in the named range. I'm hoping someone can tell me it's easy to fix! Thanks |
#2
|
|||
|
|||
Hi
we need your formula, your name definition, the expected result, etc :-) -- Regards Frank Kabel Frankfurt, Germany "Blackcat" schrieb im Newsbeitrag ... I have a number of workbooks that look at a variables workbook which has a number of named cell ranges. I have added new lines to a particular named area and now some of the formulae in the other workbooks don't work. I'm assuming it's to do with the change in the named range. I'm hoping someone can tell me it's easy to fix! Thanks |
#3
|
|||
|
|||
Did you change the range definition, to include the new rows?
Blackcat wrote: I have a number of workbooks that look at a variables workbook which has a number of named cell ranges. I have added new lines to a particular named area and now some of the formulae in the other workbooks don't work. I'm assuming it's to do with the change in the named range. I'm hoping someone can tell me it's easy to fix! Thanks -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
#4
|
|||
|
|||
If you're working with names, get Jan Karel Pieterse's (with Charles Williams
and Matthew Henson) Name Manager You can find it at: NameManager.Zip from http://www.oaltd.co.uk/mvp Maybe it'll help you find out what you really changed. Blackcat wrote: I have a number of workbooks that look at a variables workbook which has a number of named cell ranges. I have added new lines to a particular named area and now some of the formulae in the other workbooks don't work. I'm assuming it's to do with the change in the named range. I'm hoping someone can tell me it's easy to fix! Thanks -- Dave Peterson |
#5
|
|||
|
|||
Frank,
This is the formula IF($J27="",0,VLOOKUP(Y27,'CS Salary Model Variables.xls'!SalaryScales,8,FALSE)*VLOOKUP($J27, pensions,2,FALSE)*$F27) The "SalaryScales" name is the problem. It looks fine. It covers the whole area including the newly inserted parts and I can't see why the formula won't work. Thanks "Frank Kabel" wrote: Hi we need your formula, your name definition, the expected result, etc :-) -- Regards Frank Kabel Frankfurt, Germany "Blackcat" schrieb im Newsbeitrag ... I have a number of workbooks that look at a variables workbook which has a number of named cell ranges. I have added new lines to a particular named area and now some of the formulae in the other workbooks don't work. I'm assuming it's to do with the change in the named range. I'm hoping someone can tell me it's easy to fix! Thanks |
#6
|
|||
|
|||
Debra,
The changes were within the body of the range so the definition looks fine. "Debra Dalgleish" wrote: Did you change the range definition, to include the new rows? Blackcat wrote: I have a number of workbooks that look at a variables workbook which has a number of named cell ranges. I have added new lines to a particular named area and now some of the formulae in the other workbooks don't work. I'm assuming it's to do with the change in the named range. I'm hoping someone can tell me it's easy to fix! Thanks -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
#7
|
|||
|
|||
Hi
also provide the name definitions for both names -- Regards Frank Kabel Frankfurt, Germany "Blackcat" schrieb im Newsbeitrag ... Frank, This is the formula IF($J27="",0,VLOOKUP(Y27,'CS Salary Model Variables.xls'!SalaryScales,8,FALSE)*VLOOKUP($J27, pensions,2,FALSE)*$F2 7) The "SalaryScales" name is the problem. It looks fine. It covers the whole area including the newly inserted parts and I can't see why the formula won't work. Thanks "Frank Kabel" wrote: Hi we need your formula, your name definition, the expected result, etc :-) -- Regards Frank Kabel Frankfurt, Germany "Blackcat" schrieb im Newsbeitrag ... I have a number of workbooks that look at a variables workbook which has a number of named cell ranges. I have added new lines to a particular named area and now some of the formulae in the other workbooks don't work. I'm assuming it's to do with the change in the named range. I'm hoping someone can tell me it's easy to fix! Thanks |
#8
|
|||
|
|||
Hi,
SalaryScales is $E$37:$BL$639 pensions is $A$48:$F$52 It looks like the pension name is at fault although I haven't altered that! Could it be to do with the absolute references? Many Thanks "Frank Kabel" wrote: Hi also provide the name definitions for both names -- Regards Frank Kabel Frankfurt, Germany "Blackcat" schrieb im Newsbeitrag ... Frank, This is the formula IF($J27="",0,VLOOKUP(Y27,'CS Salary Model Variables.xls'!SalaryScales,8,FALSE)*VLOOKUP($J27, pensions,2,FALSE)*$F2 7) The "SalaryScales" name is the problem. It looks fine. It covers the whole area including the newly inserted parts and I can't see why the formula won't work. Thanks "Frank Kabel" wrote: Hi we need your formula, your name definition, the expected result, etc :-) -- Regards Frank Kabel Frankfurt, Germany "Blackcat" schrieb im Newsbeitrag ... I have a number of workbooks that look at a variables workbook which has a number of named cell ranges. I have added new lines to a particular named area and now some of the formulae in the other workbooks don't work. I'm assuming it's to do with the change in the named range. I'm hoping someone can tell me it's easy to fix! Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Maintaining cell reference after sorting | Excel Discussion (Misc queries) | |||
can't move a named cell without breaking a hyperlink to that cell | Links and Linking in Excel | |||
Viewing List of Named Ranges | Excel Discussion (Misc queries) | |||
Excel2K: Is it possible to use dynamic named ranges in custom data validation formula? | Excel Discussion (Misc queries) | |||
Named dynamic ranges, copied worksheets and graph source data | Charts and Charting in Excel |