| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
I have a Table that I can't Sort--Final attempt for help
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)) |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
I have a Table that I can't Sort--Final attempt for help
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. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Mike" wrote: 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)) |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
I have a Table that I can't Sort--Final attempt for help
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" wrote: 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)) |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
I have a Table that I can't Sort--Final attempt for help
Sorry
Use $BD6 instead of $BD3 "Sheeloo" wrote: 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" wrote: 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)) |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
I have a Table that I can't Sort--Final attempt for help
The first available colmn is BD
But, I'm sure I understand what am to. Could you explain a little more for me "Sheeloo" wrote: Sorry Use $BD6 instead of $BD3 "Sheeloo" wrote: 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" wrote: 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)) |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
I have a Table that I can't Sort--Final attempt for help
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" wrote: The first available colmn is BD But, I'm sure I understand what am to. Could you explain a little more for me "Sheeloo" wrote: Sorry Use $BD6 instead of $BD3 "Sheeloo" wrote: 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" wrote: 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)) |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
I have a Table that I can't Sort--Final attempt for help
Sheeloo,
Sorry had company drop in. I used the formula as you listed it and the return in the cells is +REF! Mike "Sheeloo" wrote: 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" wrote: The first available colmn is BD But, I'm sure I understand what am to. Could you explain a little more for me "Sheeloo" wrote: Sorry Use $BD6 instead of $BD3 "Sheeloo" wrote: 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" wrote: 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)) |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
I have a Table that I can't Sort--Final attempt for help
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" wrote: Sheeloo, Sorry had company drop in. I used the formula as you listed it and the return in the cells is +REF! Mike "Sheeloo" wrote: 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" wrote: The first available colmn is BD But, I'm sure I understand what am to. Could you explain a little more for me "Sheeloo" wrote: Sorry Use $BD6 instead of $BD3 "Sheeloo" wrote: 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" wrote: 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)) |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
I have a Table that I can't Sort--Final attempt for help
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" wrote: 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" wrote: Sheeloo, Sorry had company drop in. I used the formula as you listed it and the return in the cells is +REF! Mike "Sheeloo" wrote: 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" wrote: The first available colmn is BD But, I'm sure I understand what am to. Could you explain a little more for me "Sheeloo" wrote: Sorry Use $BD6 instead of $BD3 "Sheeloo" wrote: 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" wrote: 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)) |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
I have a Table that I can't Sort--Final attempt for help
No problem. Mail it at your convenience.
Have a safe flight. "Mike" wrote: 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" wrote: 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" wrote: Sheeloo, Sorry had company drop in. I used the formula as you listed it and the return in the cells is +REF! Mike "Sheeloo" wrote: 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" wrote: The first available colmn is BD But, I'm sure I understand what am to. Could you explain a little more for me "Sheeloo" wrote: Sorry Use $BD6 instead of $BD3 "Sheeloo" wrote: 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" wrote: 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)) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Sumproduct - Second Attempt | Excel Worksheet Functions | |||
| 'of' percentage with calculation (attempt 2) | Excel Discussion (Misc queries) | |||
| First attempt at VBA coding problem | New Users to Excel | |||
| 2nd attempt ~ complicated formula | Excel Worksheet Functions | |||
| Help - first time to attempt link | Links and Linking in Excel | |||
Linear Mode
