Dynamic Named Range

[Madiya]

I have a sheet which uses named ranges wich refers to the other
workbook.
It works fine.
Problem starts when I insert or delete a column in the source sheet of
other workbook.
This gives wrong referance and data fatched is wrong. I have to adjust
named ranges to get the correct data.

Is there a way which ensures the correct column referance even if I
insert or delete any column in between??


Regards,
Madiya

 

[Bob Phillips]

Shouldn't do, it should adjust the name as well. What is the formula that
you use for the named range?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Madiya]

Bob,
Thanks for your reply.
Formulas in the cell using names are giving #Ref error if I insert or
delete columns in between. I am using lot of sumproduct formulas as
given below.

Name range:
BAN ='C:\Office Docs\50K\[Wireline DPR.xls]Banwise bld
data'!$B$1:$B$7000
EDP_Installed ='C:\Office Docs\50K\[Wireline DPR.xls]Banwise bld
data'!$AB$1:$AB$7000


Formula in Cell :
=SUMPRODUCT((BAN=A3)*(EDP_Installed<>""))

I am using win2k with office2003 if that matters.

Regards,
Madiya.

 

[Bob Phillips]

Madiya,

Those are not dynamic named ranges, they are static.

However, notwithstanding terminology, I just ran a simulated test, and when
I insert a row into the 'Banwise bld data' sheet, the range extends as
expected, and the formula still works.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

Bob,
Thanks for your reply.
Formulas in the cell using names are giving #Ref error if I insert or
delete columns in between. I am using lot of sumproduct formulas as
given below.

Name range:
BAN ='C:\Office Docs\50K\[Wireline DPR.xls]Banwise bld
data'!$B$1:$B$7000
EDP_Installed ='C:\Office Docs\50K\[Wireline DPR.xls]Banwise bld
data'!$AB$1:$AB$7000


Formula in Cell :
=SUMPRODUCT((BAN=A3)*(EDP_Installed<>""))

I am using win2k with office2003 if that matters.

Regards,
Madiya.

Shouldn't do, it should adjust the name as well. What is the formula that
you use for the named range?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Madiya]

Bob,
Yes, It may be static.
But I need to insert columns and not Row.
Say, Col P contains heading as EDP in Row1.
Now if I insert a column before col P, EDP column becomes col Q.
So I want my named range EDP to refer to new col Q instead of old col
P.

I hope I am clear for my problem.

Regards,
Madiya

Madiya,

Those are not dynamic named ranges, they are static.

However, notwithstanding terminology, I just ran a simulated test, and when
I insert a row into the 'Banwise bld data' sheet, the range extends as
expected, and the formula still works.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

Bob,
Thanks for your reply.
Formulas in the cell using names are giving #Ref error if I insert or
delete columns in between. I am using lot of sumproduct formulas as
given below.

Name range:
BAN ='C:\Office Docs\50K\[Wireline DPR.xls]Banwise bld
data'!$B$1:$B$7000
EDP_Installed ='C:\Office Docs\50K\[Wireline DPR.xls]Banwise bld
data'!$AB$1:$AB$7000


Formula in Cell :
=SUMPRODUCT((BAN=A3)*(EDP_Installed<>""))

I am using win2k with office2003 if that matters.

Regards,
Madiya.

Shouldn't do, it should adjust the name as well. What is the formula that
you use for the named range?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

I have a sheet which uses named ranges wich refers to the other
workbook.
It works fine.
Problem starts when I insert or delete a column in the source sheet of
other workbook.
This gives wrong referance and data fatched is wrong. I have to adjust
named ranges to get the correct data.

Is there a way which ensures the correct column referance even if I
insert or delete any column in between??


Regards,
Madiya

 

[Bob Phillips]

That is no different, it still gets updated.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

Bob,
Yes, It may be static.
But I need to insert columns and not Row.
Say, Col P contains heading as EDP in Row1.
Now if I insert a column before col P, EDP column becomes col Q.
So I want my named range EDP to refer to new col Q instead of old col
P.

I hope I am clear for my problem.

Regards,
Madiya

Madiya,

Those are not dynamic named ranges, they are static.

However, notwithstanding terminology, I just ran a simulated test, and when
I insert a row into the 'Banwise bld data' sheet, the range extends as
expected, and the formula still works.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

Bob,
Thanks for your reply.
Formulas in the cell using names are giving #Ref error if I insert or
delete columns in between. I am using lot of sumproduct formulas as
given below.

Name range:
BAN ='C:\Office Docs\50K\[Wireline DPR.xls]Banwise bld
data'!$B$1:$B$7000
EDP_Installed ='C:\Office Docs\50K\[Wireline DPR.xls]Banwise bld
data'!$AB$1:$AB$7000


Formula in Cell :
=SUMPRODUCT((BAN=A3)*(EDP_Installed<>""))

I am using win2k with office2003 if that matters.

Regards,
Madiya.

Bob Phillips wrote:
Shouldn't do, it should adjust the name as well. What is the formula that
you use for the named range?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

I have a sheet which uses named ranges wich refers to the other
workbook.
It works fine.
Problem starts when I insert or delete a column in the source sheet of
other workbook.
This gives wrong referance and data fatched is wrong. I have to adjust
named ranges to get the correct data.

Is there a way which ensures the correct column referance even if I
insert or delete any column in between??


Regards,
Madiya

 

[Madiya]

Bob,
It doesn't happen with me.
What maight be wrong??

Regards,
Madiya

That is no different, it still gets updated.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

Bob,
Yes, It may be static.
But I need to insert columns and not Row.
Say, Col P contains heading as EDP in Row1.
Now if I insert a column before col P, EDP column becomes col Q.
So I want my named range EDP to refer to new col Q instead of old col
P.

I hope I am clear for my problem.

Regards,
Madiya

Madiya,

Those are not dynamic named ranges, they are static.

However, notwithstanding terminology, I just ran a simulated test, and when
I insert a row into the 'Banwise bld data' sheet, the range extends as
expected, and the formula still works.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

Bob,
Thanks for your reply.
Formulas in the cell using names are giving #Ref error if I insert or
delete columns in between. I am using lot of sumproduct formulas as
given below.

Name range:
BAN ='C:\Office Docs\50K\[Wireline DPR.xls]Banwise bld
data'!$B$1:$B$7000
EDP_Installed ='C:\Office Docs\50K\[Wireline DPR.xls]Banwise bld
data'!$AB$1:$AB$7000


Formula in Cell :
=SUMPRODUCT((BAN=A3)*(EDP_Installed<>""))

I am using win2k with office2003 if that matters.

Regards,
Madiya.

Bob Phillips wrote:
Shouldn't do, it should adjust the name as well. What is the formula
that
you use for the named range?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

I have a sheet which uses named ranges wich refers to the other
workbook.
It works fine.
Problem starts when I insert or delete a column in the source sheet of
other workbook.
This gives wrong referance and data fatched is wrong. I have to adjust
named ranges to get the correct data.

Is there a way which ensures the correct column referance even if I
insert or delete any column in between??


Regards,
Madiya

 

[Bob Phillips]

I can't think of anything. Can you post me the worksheet?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

Bob,
It doesn't happen with me.
What maight be wrong??

Regards,
Madiya

That is no different, it still gets updated.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

Bob,
Yes, It may be static.
But I need to insert columns and not Row.
Say, Col P contains heading as EDP in Row1.
Now if I insert a column before col P, EDP column becomes col Q.
So I want my named range EDP to refer to new col Q instead of old col
P.

I hope I am clear for my problem.

Regards,
Madiya


Bob Phillips wrote:
Madiya,

Those are not dynamic named ranges, they are static.

However, notwithstanding terminology, I just ran a simulated test,

and
when

I insert a row into the 'Banwise bld data' sheet, the range extends as
expected, and the formula still works.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

Bob,
Thanks for your reply.
Formulas in the cell using names are giving #Ref error if I insert or
delete columns in between. I am using lot of sumproduct formulas as
given below.

Name range:
BAN ='C:\Office Docs\50K\[Wireline DPR.xls]Banwise bld
data'!$B$1:$B$7000
EDP_Installed ='C:\Office Docs\50K\[Wireline DPR.xls]Banwise bld
data'!$AB$1:$AB$7000


Formula in Cell :
=SUMPRODUCT((BAN=A3)*(EDP_Installed<>""))

I am using win2k with office2003 if that matters.

Regards,
Madiya.

Bob Phillips wrote:
Shouldn't do, it should adjust the name as well. What is the formula
that
you use for the named range?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

I have a sheet which uses named ranges wich refers to the other
workbook.
It works fine.
Problem starts when I insert or delete a column in the source sheet of
other workbook.
This gives wrong referance and data fatched is wrong. I have

to
adjust

named ranges to get the correct data.

Is there a way which ensures the correct column referance even

if
I

insert or delete any column in between??


Regards,
Madiya