Sorting?

Blasted forum lost my last post.

There is a way to enter a list of "absolute" cell references without having to do each one separately.

1. Format column C as text.
2. In C2 type the text =Table 2::T$3 . It should remain as text, not become a formula
3. In C3 type the text =Table 2::T$14
4. Select both cells and fill down. You should get a pattern of T$3, T$14, T$25, T$36, etc.
5. Format the column as "automatic". The text should turn into formulas.
6. Do the same thing for column B

That is a matter of opinion. On one hand mine requires an extra column and uses two formulas which can be expanded.

The method Badunit proposes takes more steps and no formulas

DT,

I'd suggest that you circumvent the sorting issues by using LOOKUP functions to keep your references attached to the proper cells.

We'd be happy to help you with this, and it would be easier if you would show us all your talbes in screen shots so we know what we are dealing with.

Jerry

dtyron,

Did you try the solution I proposed?

OK. Try this:

In the small table (Ranking):

Add a new column (I added column B, which you can hide later).

B2=IFERROR(MATCH(A2, 2013 Pool Standings :: A, 0)+1, "")

C2=INDIRECT("'Table 2-1'::T"&B2)

select B2 and C2, then fill down

This will survive a sort

The basic concept is that a cell reference may be abolute or relative. Your original problem was that you made relative references to cells in the table "2013 Pool Standings"

a relative refernce is one that will adjust the way the cell that contains the reference adjusts (with regard to the current position of the cell containing the reference). An absolute reference in a cell will remian regardless where you make the reference.

a relative reference looks like:

B1=A1

This means in cell B1 enter the formula "=A1".... that is B1 refers to cell A1 (in a relative way)

if you copy cell B1, then select cell B2 and paste the relative reference will update and cell B2 will contain:

"=A2"

however, if you select cell A1 and enter "=$A$1" this is n absolute reference. now copy cell B1 and, then select B2 and paste. If you inspect both cells you will see they both contain "=$A$1".

In your original post you indicated the cells would change after a sort. This is because the sort moves the cells and, since you used relative references, it adjusted the reference to match-- which in your case was undesirable.

I proposed a way to make your Ranking table "sort-safe" (this is a known phenomenon). There are probably 10 ways to solve the problem.

Try more than one by making multiple ranking tables. This will allow YOU to decide what you think is easiest.

In cell B2 the formula should be:

=IFERROR(MATCH(A2, 2013 Pool Standings :: A, 0)+1, "")

that is the "B2" should not be in the cell

Also, and I should have mentioned this... the nems in column A MUST be the same as listed in the table "2013 Pool Standings"

"Larry" should be "Larry Beamer"

etc

"Norm C. " should be "Norm Charest" and all the other names should match exactly

yes. that is correct because "Larry Beamer" is in the third row