I am having a problem with named ra

[VirginiaRussell]

I am having a problem with named ranges. In my destination worksheet I am referring to named-range cells from multiple source sheets. But if one of the source sheets is SORTED, the data transferred is no longer correct, because the information in the named-range cell has been moved in the sort. How can I avoid this?!? Thanks for any help.

 

[SkipVought]

Hi Ginny!

So you have a named range on a sheet.

Let's say its named MyRange, and the range is A1:A20

So then you sort the data in A1:A20 and its in different cells, right?

Well MyRange STILL referes to A1:A20, doesn't it?

What am I missing?

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[VirginiaRussell]

The named range is actually only ONE cell; the cells surrounding the named cell contain other values....does that make sense?

 

[SkipVought]

But the RANGE does not move with sorted values, does it?

Explain what you are trying to do.

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[xlbo]

But the value that was in the 1 cell named range DOES move when you sort it ???
I assume that is the issue
'fraid there is no solution other than to
a: not sort the data
b: put your named range with data somewhere else

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[DaleWatson123321]

What's that expression ??? "Yes Virginia, there is a... solution"

...well perhaps, depending on:

a) Whether your data is structured - i.e. with unique column headings (or field names).

b) Whether the cell containing your named range is on a row that can be "isolated" by using criteria. For example the row would need to contain a unique identifier. Alternatively, more than one field could be used to "isolate" that particular row.

If the above two conditions exist, or the structure modified to meet these conditions, then it's possible to use a database function - e.g. =DGET - to provide the value you require "regardless" of whether the data is sorted, and regardless of where the value is located on the worksheet.

If you think the above is workable and would like help with setting up the DGET formula, an "easy" solution would be to email me a copy of your file - or a "simplified" version with any sensitive data replaced with fictitious data that still reflects the type of data you're working with (and need to isolate with the DGET function).

I hope this can help.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca