Sorting data and retaining additional information with value, not

M

Marie

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
 
M

Matt - Data Manager - Blue Ridge Telecom

After the user right-click, what are you using to determine the name of the
named range?
What if you don’t 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 I’d use MATCH function instead.

Matt Chen
Blue Ridge Telecom
 
M

Marie

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.
 
M

Matt - Data Manager - Blue Ridge Telecom

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.
 
Top