Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting data and retaining additional information with value, not
Hi
I am working on a project for a client and have a problem. We have a macro setup so that additional information can be viewed which relates to the data in the cell (basically, the user can right-click on the cell and one of the options is to view the additional data). We are linking to the additional data by using named ranges. The problem is: When data is sorted, the named range stays will the specific cell and does not move with the data/value in the cell. The result is incorrect association between the data and the addtional information that can be view with the right-click option. Is there a way to "tag" the value in a cell, so after a sort, the association remains with the value and not the specific cell? I noticed that comments move with the value and not the cell - but I have not been successful with anything I have tried. Any help would be appreciated. Thanks Marie |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting data and retaining additional information with value, not
After the user right-click, what are you using to determine the name of the
named range? What if you dont use the named range and use VLOOKUP/HLOOKUP function instead. From your post I am assuming the cell your user right-clicked on has a key then the macro uses the key to find the associated info from a list. If you arrange the list with the keys as the 1st column then VLOOKUP function can easily find the corresponding cells. If you are returning more than one value base on the key then Id use MATCH function instead. Matt Chen Blue Ridge Telecom "Marie" wrote: Hi I am working on a project for a client and have a problem. We have a macro setup so that additional information can be viewed which relates to the data in the cell (basically, the user can right-click on the cell and one of the options is to view the additional data). We are linking to the additional data by using named ranges. The problem is: When data is sorted, the named range stays will the specific cell and does not move with the data/value in the cell. The result is incorrect association between the data and the addtional information that can be view with the right-click option. Is there a way to "tag" the value in a cell, so after a sort, the association remains with the value and not the specific cell? I noticed that comments move with the value and not the cell - but I have not been successful with anything I have tried. Any help would be appreciated. Thanks Marie |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting data and retaining additional information with value,
This is a financial spreadsheet and so there are several cells on the same
row are named. The user is sent to different sections of the additional document based on which celll they right-click on. When they right-click and select the "view details" option we added, the code looks at the named range of the cell and uses that to know what to display. "Matt - Data Manager - Blue Ridge Telecom" wrote: After the user right-click, what are you using to determine the name of the named range? What if you dont use the named range and use VLOOKUP/HLOOKUP function instead. From your post I am assuming the cell your user right-clicked on has a key then the macro uses the key to find the associated info from a list. If you arrange the list with the keys as the 1st column then VLOOKUP function can easily find the corresponding cells. If you are returning more than one value base on the key then Id use MATCH function instead. Matt Chen Blue Ridge Telecom "Marie" wrote: Hi I am working on a project for a client and have a problem. We have a macro setup so that additional information can be viewed which relates to the data in the cell (basically, the user can right-click on the cell and one of the options is to view the additional data). We are linking to the additional data by using named ranges. The problem is: When data is sorted, the named range stays will the specific cell and does not move with the data/value in the cell. The result is incorrect association between the data and the addtional information that can be view with the right-click option. Is there a way to "tag" the value in a cell, so after a sort, the association remains with the value and not the specific cell? I noticed that comments move with the value and not the cell - but I have not been successful with anything I have tried. Any help would be appreciated. Thanks Marie |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting data and retaining additional information with value,
My suggestion is not to use a spreadsheet to act like a database.
the info that needs to be "looked up" should be in a table of a database. where it will always return expected results. If you insist on using a worksheet for this purpose and at the same time allows users to be able to temper with the data then you can't expect it to work all the time. if for some reason you have to use a worksheet then I suggest you to add some protections to it. Provide user-interfaces for adding/updating info. This way you can better control what goes where. "Marie" wrote: This is a financial spreadsheet and so there are several cells on the same row are named. The user is sent to different sections of the additional document based on which celll they right-click on. When they right-click and select the "view details" option we added, the code looks at the named range of the cell and uses that to know what to display. "Matt - Data Manager - Blue Ridge Telecom" wrote: After the user right-click, what are you using to determine the name of the named range? What if you dont use the named range and use VLOOKUP/HLOOKUP function instead. From your post I am assuming the cell your user right-clicked on has a key then the macro uses the key to find the associated info from a list. If you arrange the list with the keys as the 1st column then VLOOKUP function can easily find the corresponding cells. If you are returning more than one value base on the key then Id use MATCH function instead. Matt Chen Blue Ridge Telecom "Marie" wrote: Hi I am working on a project for a client and have a problem. We have a macro setup so that additional information can be viewed which relates to the data in the cell (basically, the user can right-click on the cell and one of the options is to view the additional data). We are linking to the additional data by using named ranges. The problem is: When data is sorted, the named range stays will the specific cell and does not move with the data/value in the cell. The result is incorrect association between the data and the addtional information that can be view with the right-click option. Is there a way to "tag" the value in a cell, so after a sort, the association remains with the value and not the specific cell? I noticed that comments move with the value and not the cell - but I have not been successful with anything I have tried. Any help would be appreciated. Thanks Marie |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to get additional information | Excel Discussion (Misc queries) | |||
Retaining additional row info on refreshing data table | Excel Discussion (Misc queries) | |||
Excel Chart - additional information added | Excel Discussion (Misc queries) | |||
Retaining cell reference in destination, when sorting source file | Excel Worksheet Functions | |||
Repost for additional information | Excel Programming |