Named Range Transfer

[Graham Haughs]

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

 

[JMay]

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]

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.

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.

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

 

[JMay]

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)

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.

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.

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

 

[JMay]

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

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)

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.

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.


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

 

[Graham Haughs]

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.

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

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)

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.


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

 

[Duke Carey]

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.

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.

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.

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

 

[brfiel]

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

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.

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

 

[Tom Ogilvy]

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

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.

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.

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.


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

 

[Graham Haughs]

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

Graham