I have worksheets listing score totals for individuals in a tournament. Some
people are in more than one tournament, hence, more than one worksheet.

When I create an +overall" worksheet that links to the separate tournaments,
I can add all the points together for people who are in more than one
tournament, but when I sort the orginal worksheets, it messes up the order in
the "overall" worksheet.

Is there a way to make the linked cells stay where they are out? I have
tried naming cells and making them absolute, but a sort in tournament 1,
moves the "overall" cells in column 2, but not the other cells in that row.

It sounds like you have formulas that look like
=sheet2!a1

With that kind of formula, you're gonna be out of luck. (well, unless you can
sort the original range???).

But you may be able to use another formula.

If you can pick out a column that is unique in both spots (names/id kind of
thing), then you could use:

=vlookup()
or
=index(match())
to retrieve values.

(the key value used to match would be typed in (or copied)--not a formula.

If you want to read more, Debra Dalgleish's has some notes you may like:
http://www.contextures.com/xlFunctions02.html (for =vlookup())
and
http://www.contextures.com/xlFunctions03.html (for =index(match()))

karen wrote:

I have worksheets listing score totals for individuals in a tournament. Some
people are in more than one tournament, hence, more than one worksheet.

When I create an +overall" worksheet that links to the separate tournaments,
I can add all the points together for people who are in more than one
tournament, but when I sort the orginal worksheets, it messes up the order in
the "overall" worksheet.

Is there a way to make the linked cells stay where they are out? I have
tried naming cells and making them absolute, but a sort in tournament 1,
moves the "overall" cells in column 2, but not the other cells in that row.

--

Dave Peterson

Thanks for the information. I tried a VLookup, but if I re-sort the origin
page, the destination page re-sorts as well even if I name the cells.

If there is not a way to lock the rows on the destination page (i.e. make
sure the linked cells remain together for the name in the beginning cell of
the row), I think I am out of luck and will do a "Save As" when I want to
sort the original document by point leader.

Thanks again.
Karen

"Dave Peterson" wrote:

It sounds like you have formulas that look like
=sheet2!a1

With that kind of formula, you're gonna be out of luck. (well, unless you can
sort the original range???).

But you may be able to use another formula.

If you can pick out a column that is unique in both spots (names/id kind of
thing), then you could use:

=vlookup()
or
=index(match())
to retrieve values.

(the key value used to match would be typed in (or copied)--not a formula.

If you want to read more, Debra Dalgleish's has some notes you may like:
http://www.contextures.com/xlFunctions02.html (for =vlookup())
and
http://www.contextures.com/xlFunctions03.html (for =index(match()))

karen wrote:

I have worksheets listing score totals for individuals in a tournament. Some
people are in more than one tournament, hence, more than one worksheet.

When I create an +overall" worksheet that links to the separate tournaments,
I can add all the points together for people who are in more than one
tournament, but when I sort the orginal worksheets, it messes up the order in
the "overall" worksheet.

Is there a way to make the linked cells stay where they are out? I have
tried naming cells and making them absolute, but a sort in tournament 1,
moves the "overall" cells in column 2, but not the other cells in that row.

--

Dave Peterson

If your key value (column of values) is a value (not a formula), then the
=vlookup() formulas shouldn't change when you sort the original.

You could save a copy, convert it to values and do anything you wanted to the
data.

karen wrote:

Thanks for the information. I tried a VLookup, but if I re-sort the origin
page, the destination page re-sorts as well even if I name the cells.

If there is not a way to lock the rows on the destination page (i.e. make
sure the linked cells remain together for the name in the beginning cell of
the row), I think I am out of luck and will do a "Save As" when I want to
sort the original document by point leader.

Thanks again.
Karen

"Dave Peterson" wrote:

It sounds like you have formulas that look like
=sheet2!a1

With that kind of formula, you're gonna be out of luck. (well, unless you can
sort the original range???).

But you may be able to use another formula.

If you can pick out a column that is unique in both spots (names/id kind of
thing), then you could use:

=vlookup()
or
=index(match())
to retrieve values.

(the key value used to match would be typed in (or copied)--not a formula.

If you want to read more, Debra Dalgleish's has some notes you may like:
http://www.contextures.com/xlFunctions02.html (for =vlookup())
and
http://www.contextures.com/xlFunctions03.html (for =index(match()))

karen wrote:

I have worksheets listing score totals for individuals in a tournament. Some
people are in more than one tournament, hence, more than one worksheet.

When I create an +overall" worksheet that links to the separate tournaments,
I can add all the points together for people who are in more than one
tournament, but when I sort the orginal worksheets, it messes up the order in
the "overall" worksheet.

Is there a way to make the linked cells stay where they are out? I have
tried naming cells and making them absolute, but a sort in tournament 1,
moves the "overall" cells in column 2, but not the other cells in that row.

--

Dave Peterson


--

Dave Peterson

Yes, my key value is a formula. How do I convert it to values in the copy?
Karen

"Dave Peterson" wrote:

If your key value (column of values) is a value (not a formula), then the
=vlookup() formulas shouldn't change when you sort the original.

You could save a copy, convert it to values and do anything you wanted to the
data.

karen wrote:

Thanks for the information. I tried a VLookup, but if I re-sort the origin
page, the destination page re-sorts as well even if I name the cells.

If there is not a way to lock the rows on the destination page (i.e. make
sure the linked cells remain together for the name in the beginning cell of
the row), I think I am out of luck and will do a "Save As" when I want to
sort the original document by point leader.

Thanks again.
Karen

"Dave Peterson" wrote:

It sounds like you have formulas that look like
=sheet2!a1

With that kind of formula, you're gonna be out of luck. (well, unless you can
sort the original range???).

But you may be able to use another formula.

If you can pick out a column that is unique in both spots (names/id kind of
thing), then you could use:

=vlookup()
or
=index(match())
to retrieve values.

(the key value used to match would be typed in (or copied)--not a formula.

If you want to read more, Debra Dalgleish's has some notes you may like:
http://www.contextures.com/xlFunctions02.html (for =vlookup())
and
http://www.contextures.com/xlFunctions03.html (for =index(match()))

karen wrote:

I have worksheets listing score totals for individuals in a tournament. Some
people are in more than one tournament, hence, more than one worksheet.

When I create an +overall" worksheet that links to the separate tournaments,
I can add all the points together for people who are in more than one
tournament, but when I sort the orginal worksheets, it messes up the order in
the "overall" worksheet.

Is there a way to make the linked cells stay where they are out? I have
tried naming cells and making them absolute, but a sort in tournament 1,
moves the "overall" cells in column 2, but not the other cells in that row.

--

Dave Peterson


--

Dave Peterson

Select the range to convert to values
edit|copy
edit|paste special|Values



karen wrote:

Yes, my key value is a formula. How do I convert it to values in the copy?
Karen

"Dave Peterson" wrote:

If your key value (column of values) is a value (not a formula), then the
=vlookup() formulas shouldn't change when you sort the original.

You could save a copy, convert it to values and do anything you wanted to the
data.

karen wrote:

Thanks for the information. I tried a VLookup, but if I re-sort the origin
page, the destination page re-sorts as well even if I name the cells.

If there is not a way to lock the rows on the destination page (i.e. make
sure the linked cells remain together for the name in the beginning cell of
the row), I think I am out of luck and will do a "Save As" when I want to
sort the original document by point leader.

Thanks again.
Karen

"Dave Peterson" wrote:

It sounds like you have formulas that look like
=sheet2!a1

With that kind of formula, you're gonna be out of luck. (well, unless you can
sort the original range???).

But you may be able to use another formula.

If you can pick out a column that is unique in both spots (names/id kind of
thing), then you could use:

=vlookup()
or
=index(match())
to retrieve values.

(the key value used to match would be typed in (or copied)--not a formula.

If you want to read more, Debra Dalgleish's has some notes you may like:
http://www.contextures.com/xlFunctions02.html (for =vlookup())
and
http://www.contextures.com/xlFunctions03.html (for =index(match()))

karen wrote:

I have worksheets listing score totals for individuals in a tournament. Some
people are in more than one tournament, hence, more than one worksheet.

When I create an +overall" worksheet that links to the separate tournaments,
I can add all the points together for people who are in more than one
tournament, but when I sort the orginal worksheets, it messes up the order in
the "overall" worksheet.

Is there a way to make the linked cells stay where they are out? I have
tried naming cells and making them absolute, but a sort in tournament 1,
moves the "overall" cells in column 2, but not the other cells in that row.

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson