how to sort correctly with this formula being used

[Brad]

I have a worksheet we use as a printout, part of that sheet has a formula(s)
we use for ease of use to update the sheets weekly informtion.
Just before we do the print out we resort the data based on one column of
information.

The problem I have is this, I have two columns of information using formulas
that won't sort along with the rest of the information.
this formula: =INDIRECT("Scorecard!X"&2*(ROW()-3)+2)
and this formula: =INDIRECT("Scorecard!X"&2*(ROW()-2)+1), are keeping my
data from sorting correctly.

My column headings are this;
Team, Name#1, Ph#, Hdcp, Points, Hdcp, Name#2, Ph#

The formulas are for the two 'Hdcp' columns but I need to sort by the
'Points' column.

I highlight the group of cells, column headings across by rows down and then
I sort by column 'Points' in decending order. All the information sorts
correctly except the 'Hdcp' columns. The data in those columns stays as if I
didn't sort the information.

FYI, the formula explaination is in forum question;
"drag formula down making chgs on the way (Q2) 6-3-09"

Any advise?
Brad

 

[Shane Devenshire]

Hi,

You could try rearranging the column order and then excluding the helper
columns from the sort range

Team, Name#1, Ph#, Points, Name#2, Ph#, Hdcp, Hdcp

Without your data its hard to know if this will work, but you could try it.

 

[Brad]

Shane,

I'm assuming by 'helper columns' you mean the 'hdcp' columns and no that
won't work.

The hdcp = handicap and the guys (golfers) want those on the page so they
can keep track of their handicaps and how they're doing plus they want to be
able to see if the person they're playing against has a higher or lower
handicap than themselves.

Low hdcp = good golfer, High hdcp = poor golfer.

Originally we didn't sort the sheet before printing but now the guys want to
see who's in first place, second, etc by the order that they're on the page.

I thought of a couple ways to make this happen but it will take some
reworking of my spreadsheet layout and/or some major additions. I was trying
to avoid any major re-working and I was trying to make things easier for the
league secretary but I fix one problem and create another.

** I don't even use the spreadsheet, I'm just helping out to make the league
secretaries job easier calculating the handicaps, scores and team positions
and schedules.

Does your email accept attachments or is there a place (email) I can send a
copy to you and you can see / understand what I'm trying to accomplish?

Brad