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

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" wrote in message
oups.com...
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,
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)

"Madiya" wrote in message
oups.com...
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,

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)

"Madiya" wrote in message
ups.com...
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)

"Madiya" wrote in message
oups.com...
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,
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)

"Madiya" wrote in message
ups.com...
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)

"Madiya" wrote in message
oups.com...
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

That is no different, it still gets updated.

--
HTH

Bob Phillips

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

"Madiya" wrote in message
ups.com...
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)

"Madiya" wrote in message
ups.com...
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)

"Madiya" wrote in message
oups.com...
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,
It doesn't happen with me.
What maight be wrong??

Regards,
Madiya
Bob Phillips wrote:
That is no different, it still gets updated.

--
HTH

Bob Phillips

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

"Madiya" wrote in message
ups.com...
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)

"Madiya" wrote in message
ups.com...
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)

"Madiya" wrote in message
oups.com...
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

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)

"Madiya" wrote in message
oups.com...
Bob,
It doesn't happen with me.
What maight be wrong??

Regards,
Madiya
Bob Phillips wrote:
That is no different, it still gets updated.

--
HTH

Bob Phillips

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

"Madiya" wrote in message
ups.com...
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)

"Madiya" wrote in message
ups.com...
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)

"Madiya" wrote in message
oups.com...
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