I have a Table that I can't Sort--Final attempt for help

[Mike]

I have posted this question previously.

This is my last attempt to see if someone has another formula that works...

I have a table in a worksheet with column headings in 5A through 5BC each
transaction is entered in rows, starting with row 6 through row 250. The
Filter is turned on in the column headings. When I click on the arrow I can
choose a filter sort. Does the formula prevent the Sort Choice???
When I do nothing happens.

If I copy the sheet to another sheet and paste
using Special Paste and paste only Values and Number Formats the filter and
sort works perfectly.

Below are two formulas we tryed but don't allow the Sort.

=INDEX(INDIRECT("'Transaction
Database'!R5C"&(ROW()-3)&":R59C"&(ROW()-3),FALSE),COLUMN())

=INDIRECT("'transaction database'!"&ADDRESS(COLUMN()+4,(COLUMN())+ROW()-4))

 

[Luke M]

You're correct, this formula will not allow itself to be formatted, because
once you move it to a different row, it gives a different result, thus
defeating the purpose for sorting it.

 

[Sheeloo]

Mike,

You can do the following;
In the first unused column enter the formula
=Row()-3
and copy down. Then copy this column and paste special as values on itself.

Suppose this column is BD then in your formula replace
(ROW()-3)by
$BD3

Now you can sort your data... just include Col BD in your range.

Let me know how it goes...




Then in the formula

 

[Sheeloo]

Sorry
Use $BD6 instead of $BD3

Mike,

You can do the following;
In the first unused column enter the formula
=Row()-3
and copy down. Then copy this column and paste special as values on itself.

Suppose this column is BD then in your formula replace
(ROW()-3)by
$BD3

Now you can sort your data... just include Col BD in your range.

Let me know how it goes...




Then in the formula

 

[Mike]

The first available colmn is BD
But, I'm sure I understand what am to. Could you explain a little more for me

 

[Sheeloo]

In your formula
Row() - 3 gives you 3 in Row 6...
When you sort you still get Row() in the formula so that it still refers to
C3...C59 in the other sheet...

Now when you have 3,4,5,... in BD6, BD7 and refer to BD6 in the formula the
numbers in BD6 changes from 3 to something else as per the sort (say 10) so
that the formula refers to L3..L59 in the other sheet

Hope this makes sense... did you try it out?

Instead of
=INDEX(INDIRECT("'Transaction
Database'!R5C"&(ROW()-3)&":R59C"&(ROW()-3),FALSE),COLUMN())

Use
=INDEX(INDIRECT("'Transaction
Database'!R5C"&$D6&":R59C"&$D6,FALSE),COLUMN())

$ does not change D to E if you copy right
and does change 6 to 7 down the rows

 

[Mike]

Sheeloo,

Sorry had company drop in. I used the formula as you listed it and the
return in the cells is +REF!

Mike

 

[Sheeloo]

I must be having a bad day...

I dropped the B from BD

Use
=INDEX(INDIRECT("'Transaction
Database'!R5C"&$BD6&":R59C"&$BD6,FALSE),COLUMN())

If it still does not work then you may send the workbook to me at my id
to_sheeloo which is @hotmail.com

 

[Mike]

Sheeloo,
Sorry itmust be me. If you don't mind I will email you the test Book
Tomorrow night.
I have to pack and catch a flight to Boston.

Thanks for all your help and patience. It is greatly appreciated...

Mike

 

[Sheeloo]

No problem. Mail it at your convenience.

Have a safe flight.