Go Back   Access World Forums > Apps and Windows > Excel

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 06-14-2019, 08:46 AM   #1
DanG
Newly Registered User
 
DanG's Avatar
 
Join Date: Nov 2004
Posts: 477
Thanks: 1
Thanked 1 Time in 1 Post
DanG is an unknown quantity at this point
Sorting Tables

Hello,

I am new to working with tables and have an issue when sorting. The issue seems to be related to the relative positioning when referencing an adjacent cell. Before sorting, the top cell and all the ones below show the correct relative action ($b2, $b3, $b4...). But after sorting it becomes random ($b2, $b17, $b6...).

I have the formula below for the "Advisor" column and all works perfect before sorting. I am showing the formulas for the 2 top cells so you can see the difference. I have underlined and used bold text to highlight the problem.

Before sorting:
Code:
=INDEX(AdvGrpTable[AdvisorName], MATCH(Activity!$B2,AdvGrpTable[AdvGA'#], 0))

=INDEX(AdvGrpTable[AdvisorName], MATCH(Activity!$B3,AdvGrpTable[AdvGA'#], 0))
After sorting (same top 2 cells):
Code:
=INDEX(AdvGrpTable[AdvisorName], MATCH(Activity!$B3,AdvGrpTable[AdvGA'#], 0))

=INDEX(AdvGrpTable[AdvisorName], MATCH(Activity!$B2,AdvGrpTable[AdvGA'#], 0))
Yes, you are seeing that correctly. $b cell reference does not change in a relative manner.

I am lost on this one?

Thank you

__________________
You just pick up a chord, go twang, and you're got music
~ Sid Vicious

I use A03
DanG is offline   Reply With Quote
Old 06-14-2019, 10:12 AM   #2
DanG
Newly Registered User
 
DanG's Avatar
 
Join Date: Nov 2004
Posts: 477
Thanks: 1
Thanked 1 Time in 1 Post
DanG is an unknown quantity at this point
Re: Sorting Tables

I think I found the solution.

Apparently, tables has an issue with hard references sometimes. So using a dynamic reference seems to be a good workaround, at least it worked for me.

I used this instead:
Code:
=INDEX(AdvGrpTable[AdvisorName], MATCH(INDIRECT("$b"&ROW()),AdvGrpTable[AdvGA'#], 0))
__________________
You just pick up a chord, go twang, and you're got music
~ Sid Vicious

I use A03
DanG is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Sorting of Tables Djblois Tables 2 08-12-2010 11:37 AM
Sorting Problems with linked tables simonsimon Queries 1 10-22-2008 07:01 PM
Sorting Tables SteveF Tables 2 10-04-2008 02:24 PM
[SOLVED] Trouble Sorting out tables and relations Haag Tables 0 11-13-2004 02:26 PM
FORMS - sorting data in tables. eaglei66 Forms 3 04-07-2003 04:22 PM




All times are GMT -8. The time now is 09:17 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World