Named Range Transfer
 

I have a program which has a named range as shown below, which is one of
many named ranges.

=OFFSET(Fields!$A$5,0,0,COUNTA(Fields!$A:$A)-5,6)

Now someone has tried to copy the program and in doing so has
transformed this particular named range to the convoluted mix shown below.

=OFFSET('C:\Dev\projects\central
fabrication\[6Fields.xls]Fields'!$A$5,0,0,COUNTA('C:\Dev\projects\central
fabrication\[6Fields.xls]Fields'!$A:$A)-5,6)

What I would be most grateful for is for someone to tell me, step by
step if possible, exactly what they think would have been done with the
original program or named range to arrive at this situation. I cannot
see how copying the program itself to the directories in the C drive
detailed would do this, I can only think there has been some copying
and pasting to a clean sheet but I just cannot work out how or what. I
would value any guidance at all on the mechanisms that were involved in
this conversion.

Kind Regards
Graham Haughs
Turriff
Scotland

Named Range Transfer
 

Is 6Fields.xls a separate workbook? Looks like it is.. If so Open it
along with your active Rangenamed book and re-examine the Name Refers to
box; even altering it to suit. Save your file.

"Graham Haughs" wrote in message
:

I have a program which has a named range as shown below, which is one of
many named ranges.

=OFFSET(Fields!$A$5,0,0,COUNTA(Fields!$A:$A)-5,6)

Now someone has tried to copy the program and in doing so has
transformed this particular named range to the convoluted mix shown below.

=OFFSET('C:\Dev\projects\central
fabrication\[6Fields.xls]Fields'!$A$5,0,0,COUNTA('C:\Dev\projects\central
fabrication\[6Fields.xls]Fields'!$A:$A)-5,6)

What I would be most grateful for is for someone to tell me, step by
step if possible, exactly what they think would have been done with the
original program or named range to arrive at this situation. I cannot
see how copying the program itself to the directories in the C drive
detailed would do this, I can only think there has been some copying
and pasting to a clean sheet but I just cannot work out how or what. I
would value any guidance at all on the mechanisms that were involved in
this conversion.

Kind Regards
Graham Haughs
Turriff
Scotland

Named Range Transfer
 

Thanks for quick feedback. To explain a bit more to clarify, someone has
been a bit "naughty" (my kindest language at the moment) and copied a
program and modified cosmetically. All the named ranges other than this
one relate to sheets in the workbook which still exist. The sheet
"Fields" no longer exists in the copy program which I think caused this
aberration.Yes I think the workbook has been called 6Fields.xls that it
was copied to but I am trying to understand the mechanisms of how it was
arrived at from the original, I don't need to sort it.
JMay wrote:
Is 6Fields.xls a separate workbook? Looks like it is.. If so Open it
along with your active Rangenamed book and re-examine the Name Refers to
box; even altering it to suit. Save your file.

"Graham Haughs" wrote in message
:

I have a program which has a named range as shown below, which is one of
many named ranges.

=OFFSET(Fields!$A$5,0,0,COUNTA(Fields!$A:$A)-5,6)

Now someone has tried to copy the program and in doing so has
transformed this particular named range to the convoluted mix shown
below.

=OFFSET('C:\Dev\projects\central
fabrication\[6Fields.xls]Fields'!$A$5,0,0,COUNTA('C:\Dev\projects\central
fabrication\[6Fields.xls]Fields'!$A:$A)-5,6)

What I would be most grateful for is for someone to tell me, step by
step if possible, exactly what they think would have been done with the
original program or named range to arrive at this situation. I cannot
see how copying the program itself to the directories in the C drive
detailed would do this, I can only think there has been some copying
and pasting to a clean sheet but I just cannot work out how or what. I
would value any guidance at all on the mechanisms that were involved in
this conversion.

Kind Regards
Graham Haughs
Turriff
Scotland

Named Range Transfer
 

Well AT PRESENT the below formula which appears in the RefersTo:
Box of a NamedRange say Test
Test can be used to refer to the range:
A?R?:F?C?

where ?R? is determined by
The total non-blank rows in Column A minus 5 -- say equals to 50
so formula brings back 50 rows beginning at A5 down to A49

AND ?C? brings back 6 Columns (Thru Col F)

So test = A5:F49

Does that help?


=OFFSET(Fields!$A$5,0,0,COUNTA(Fields!$A:$A)-5,6)

"Graham Haughs" wrote in message
:

Thanks for quick feedback. To explain a bit more to clarify, someone has
been a bit "naughty" (my kindest language at the moment) and copied a
program and modified cosmetically. All the named ranges other than this
one relate to sheets in the workbook which still exist. The sheet
"Fields" no longer exists in the copy program which I think caused this
aberration.Yes I think the workbook has been called 6Fields.xls that it
was copied to but I am trying to understand the mechanisms of how it was
arrived at from the original, I don't need to sort it.
JMay wrote:
Is 6Fields.xls a separate workbook? Looks like it is.. If so Open it
along with your active Rangenamed book and re-examine the Name Refers to
box; even altering it to suit. Save your file.

"Graham Haughs" wrote in message
:

I have a program which has a named range as shown below, which is one of
many named ranges.

=OFFSET(Fields!$A$5,0,0,COUNTA(Fields!$A:$A)-5,6)

Now someone has tried to copy the program and in doing so has
transformed this particular named range to the convoluted mix shown
below.

=OFFSET('C:\Dev\projects\central
fabrication\[6Fields.xls]Fields'!$A$5,0,0,COUNTA('C:\Dev\projects\central
fabrication\[6Fields.xls]Fields'!$A:$A)-5,6)

What I would be most grateful for is for someone to tell me, step by
step if possible, exactly what they think would have been done with the
original program or named range to arrive at this situation. I cannot
see how copying the program itself to the directories in the C drive
detailed would do this, I can only think there has been some copying
and pasting to a clean sheet but I just cannot work out how or what. I
would value any guidance at all on the mechanisms that were involved in
this conversion.

Kind Regards
Graham Haughs
Turriff
Scotland

Named Range Transfer
 

I sorta screwed up my explanation <<sorry
*REVISED*

Test refers to the range:
A5:F?C? $A$5 regardless as it is the ANCHOR Cell



"JMay" wrote in message
:

Well AT PRESENT the below formula which appears in the RefersTo:
Box of a NamedRange say Test
Test can be used to refer to the range:
A?R?:F?C?

where ?R? is determined by
The total non-blank rows in Column A minus 5 -- say equals to 50
so formula brings back 50 rows beginning at A5 down to A49

AND ?C? brings back 6 Columns (Thru Col F)

So test = A5:F49

Does that help?


=OFFSET(Fields!$A$5,0,0,COUNTA(Fields!$A:$A)-5,6)

"Graham Haughs" wrote in message
:

Thanks for quick feedback. To explain a bit more to clarify, someone has
been a bit "naughty" (my kindest language at the moment) and copied a
program and modified cosmetically. All the named ranges other than this
one relate to sheets in the workbook which still exist. The sheet
"Fields" no longer exists in the copy program which I think caused this
aberration.Yes I think the workbook has been called 6Fields.xls that it
was copied to but I am trying to understand the mechanisms of how it was
arrived at from the original, I don't need to sort it.
JMay wrote:
Is 6Fields.xls a separate workbook? Looks like it is.. If so Open it
along with your active Rangenamed book and re-examine the Name Refers to
box; even altering it to suit. Save your file.

"Graham Haughs" wrote in message
:

I have a program which has a named range as shown below, which is one of
many named ranges.

=OFFSET(Fields!$A$5,0,0,COUNTA(Fields!$A:$A)-5,6)

Now someone has tried to copy the program and in doing so has
transformed this particular named range to the convoluted mix shown
below.

=OFFSET('C:\Dev\projects\central
fabrication\[6Fields.xls]Fields'!$A$5,0,0,COUNTA('C:\Dev\projects\central
fabrication\[6Fields.xls]Fields'!$A:$A)-5,6)

What I would be most grateful for is for someone to tell me, step by
step if possible, exactly what they think would have been done with the
original program or named range to arrive at this situation. I cannot
see how copying the program itself to the directories in the C drive
detailed would do this, I can only think there has been some copying
and pasting to a clean sheet but I just cannot work out how or what. I
would value any guidance at all on the mechanisms that were involved in
this conversion.

Kind Regards
Graham Haughs
Turriff
Scotland

Named Range Transfer
 

I obviously sorta screwed up my explanation as well for which I
apologise. To try this again I know exactly what the formula below does
=OFFSET(Fields!$A$5,0,0,COUNTA(Fields!$A:$A)-5,6)
This is not a problem. What I want to know is how someone took this
named range without asking shall we say, put it in another spreadsheet
and finished up with the range changed to
=OFFSET('C:\Dev\projects\central
fabrication\[6Fields.xls]Fields'!$A$5,0,0,COUNTA('C:\Dev\projects\central
fabrication\[6Fields.xls]Fields'!$A:$A)-5,6)
I am trying to see the process he went through to do the copy. I assume
that the file he copied to was stored in C:\Dev\projects\central
fabrication\ and the file name was 6Fields.xls, (although the square
brackets are confusing me) As I have been writing this I have just seen
what was done. A workbook was created 6Fields.xls then for some reason a
copy and paste was done to another workbook which would cause this
effect. Thanks for all your help and apologies for my efforts at
confusing everybody.
Graham
JMay wrote:
I sorta screwed up my explanation <<sorry
*REVISED*

Test refers to the range:
A5:F?C? $A$5 regardless as it is the ANCHOR Cell



"JMay" wrote in message
:

Well AT PRESENT the below formula which appears in the RefersTo:
Box of a NamedRange say Test
Test can be used to refer to the range:
A?R?:F?C?

where ?R? is determined by
The total non-blank rows in Column A minus 5 -- say equals to 50
so formula brings back 50 rows beginning at A5 down to A49

AND ?C? brings back 6 Columns (Thru Col F)

So test = A5:F49

Does that help?


=OFFSET(Fields!$A$5,0,0,COUNTA(Fields!$A:$A)-5,6)

"Graham Haughs" wrote in message
:

Thanks for quick feedback. To explain a bit more to clarify, someone
has
been a bit "naughty" (my kindest language at the moment) and copied a
program and modified cosmetically. All the named ranges other than this
one relate to sheets in the workbook which still exist. The sheet
"Fields" no longer exists in the copy program which I think caused this
aberration.Yes I think the workbook has been called 6Fields.xls that it
was copied to but I am trying to understand the mechanisms of how it
was
arrived at from the original, I don't need to sort it.
JMay wrote:
Is 6Fields.xls a separate workbook? Looks like it is.. If so
Open it
along with your active Rangenamed book and re-examine the Name
Refers to
box; even altering it to suit. Save your file.

"Graham Haughs" wrote in message
:

I have a program which has a named range as shown below, which is
one of
many named ranges.

=OFFSET(Fields!$A$5,0,0,COUNTA(Fields!$A:$A)-5,6)

Now someone has tried to copy the program and in doing so has
transformed this particular named range to the convoluted mix shown
below.

=OFFSET('C:\Dev\projects\central

fabrication\[6Fields.xls]Fields'!$A$5,0,0,COUNTA('C:\Dev\projects\central
fabrication\[6Fields.xls]Fields'!$A:$A)-5,6)

What I would be most grateful for is for someone to tell me, step by
step if possible, exactly what they think would have been done
with the
original program or named range to arrive at this situation. I
cannot
see how copying the program itself to the directories in the C drive
detailed would do this, I can only think there has been some
copying
and pasting to a clean sheet but I just cannot work out how or
what. I
would value any guidance at all on the mechanisms that were
involved in
this conversion.

Kind Regards
Graham Haughs
Turriff
Scotland

Named Range Transfer
 

Looks like somebody right-clicked on the Fields tab in the orginal workbook
and selected Move or Copy... then moved the sheet to a new workbook named
6Fields. Excel simply kept track of where the named range was tranferred.


"Graham Haughs" wrote:

Thanks for quick feedback. To explain a bit more to clarify, someone has
been a bit "naughty" (my kindest language at the moment) and copied a
program and modified cosmetically. All the named ranges other than this
one relate to sheets in the workbook which still exist. The sheet
"Fields" no longer exists in the copy program which I think caused this
aberration.Yes I think the workbook has been called 6Fields.xls that it
was copied to but I am trying to understand the mechanisms of how it was
arrived at from the original, I don't need to sort it.
JMay wrote:
Is 6Fields.xls a separate workbook? Looks like it is.. If so Open it
along with your active Rangenamed book and re-examine the Name Refers to
box; even altering it to suit. Save your file.

"Graham Haughs" wrote in message
:

I have a program which has a named range as shown below, which is one of
many named ranges.

=OFFSET(Fields!$A$5,0,0,COUNTA(Fields!$A:$A)-5,6)

Now someone has tried to copy the program and in doing so has
transformed this particular named range to the convoluted mix shown
below.

=OFFSET('C:\Dev\projects\central
fabrication\[6Fields.xls]Fields'!$A$5,0,0,COUNTA('C:\Dev\projects\central
fabrication\[6Fields.xls]Fields'!$A:$A)-5,6)

What I would be most grateful for is for someone to tell me, step by
step if possible, exactly what they think would have been done with the
original program or named range to arrive at this situation. I cannot
see how copying the program itself to the directories in the C drive
detailed would do this, I can only think there has been some copying
and pasting to a clean sheet but I just cannot work out how or what. I
would value any guidance at all on the mechanisms that were involved in
this conversion.

Kind Regards
Graham Haughs
Turriff
Scotland

Named Range Transfer
 

I'm sorry to interrupt, but I cannot start a new thread. Do I need to change
security settings or something? Thanks, Bob

"JMay" wrote:

Is 6Fields.xls a separate workbook? Looks like it is.. If so Open it
along with your active Rangenamed book and re-examine the Name Refers to
box; even altering it to suit. Save your file.

"Graham Haughs" wrote in message
:

I have a program which has a named range as shown below, which is one of
many named ranges.

=OFFSET(Fields!$A$5,0,0,COUNTA(Fields!$A:$A)-5,6)

Now someone has tried to copy the program and in doing so has
transformed this particular named range to the convoluted mix shown below.

=OFFSET('C:\Dev\projects\central
fabrication\[6Fields.xls]Fields'!$A$5,0,0,COUNTA('C:\Dev\projects\central
fabrication\[6Fields.xls]Fields'!$A:$A)-5,6)

What I would be most grateful for is for someone to tell me, step by
step if possible, exactly what they think would have been done with the
original program or named range to arrive at this situation. I cannot
see how copying the program itself to the directories in the C drive
detailed would do this, I can only think there has been some copying
and pasting to a clean sheet but I just cannot work out how or what. I
would value any guidance at all on the mechanisms that were involved in
this conversion.

Kind Regards
Graham Haughs
Turriff
Scotland

Named Range Transfer
 

I someone took your workbook and did a saveas to name the workbook
6Fields.xls in the location you show. They then reopened your workbook so
both copies were open. From the copied workbook they copied cells that
included a reference to the named range. When they pasted in the original
workbook they got the prompt that that names already exists in the
destination workbook and a choice to use that name or not. I assume they
chose Not and gave it a new name.

--
Regards,
Tom Ogilvy


"Duke Carey" wrote:

Looks like somebody right-clicked on the Fields tab in the orginal workbook
and selected Move or Copy... then moved the sheet to a new workbook named
6Fields. Excel simply kept track of where the named range was tranferred.


"Graham Haughs" wrote:

Thanks for quick feedback. To explain a bit more to clarify, someone has
been a bit "naughty" (my kindest language at the moment) and copied a
program and modified cosmetically. All the named ranges other than this
one relate to sheets in the workbook which still exist. The sheet
"Fields" no longer exists in the copy program which I think caused this
aberration.Yes I think the workbook has been called 6Fields.xls that it
was copied to but I am trying to understand the mechanisms of how it was
arrived at from the original, I don't need to sort it.
JMay wrote:
Is 6Fields.xls a separate workbook? Looks like it is.. If so Open it
along with your active Rangenamed book and re-examine the Name Refers to
box; even altering it to suit. Save your file.

"Graham Haughs" wrote in message
:

I have a program which has a named range as shown below, which is one of
many named ranges.

=OFFSET(Fields!$A$5,0,0,COUNTA(Fields!$A:$A)-5,6)

Now someone has tried to copy the program and in doing so has
transformed this particular named range to the convoluted mix shown
below.

=OFFSET('C:\Dev\projects\central
fabrication\[6Fields.xls]Fields'!$A$5,0,0,COUNTA('C:\Dev\projects\central
fabrication\[6Fields.xls]Fields'!$A:$A)-5,6)

What I would be most grateful for is for someone to tell me, step by
step if possible, exactly what they think would have been done with the
original program or named range to arrive at this situation. I cannot
see how copying the program itself to the directories in the C drive
detailed would do this, I can only think there has been some copying
and pasting to a clean sheet but I just cannot work out how or what. I
would value any guidance at all on the mechanisms that were involved in
this conversion.

Kind Regards
Graham Haughs
Turriff
Scotland

Named Range Transfer
 

Thanks Tom, that just puts the final piece of the puzzle into place.

Graham

Tom Ogilvy wrote:
I someone took your workbook and did a saveas to name the workbook
6Fields.xls in the location you show. They then reopened your workbook so
both copies were open. From the copied workbook they copied cells that
included a reference to the named range. When they pasted in the original
workbook they got the prompt that that names already exists in the
destination workbook and a choice to use that name or not. I assume they
chose Not and gave it a new name.