Re: link cells

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

This accomplished exactly what I set out to do, thanks!

Now, perhaps you could help me take it one step further. I have an autosort
function in my workbook coding (see below) to automatically place the top
employees at the top of the list (there are a few other criteria I have it
sort my employees by as you'll see in the coding). Here's the problem: I rate
the employees in 4 seperate categories, so it's the average score of those
categories that determines my overall score. If this were a static
spread***, I would just type a simple formula in (=average(c3:f3)) cell G3.
Since the autosort function removes all that is in that row (between A3:s3),
the formula is lost during the autosort. This is a problem because the next
employee entered doesn't have that formula. Is there a way to have a
permanent formula in cell G3?

Anyhow, here is my coding as of right now:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("R3")) Is Nothing Then
Exit Sub
End If

Application.EnableEvents = False
Columns("A:S").Sort _
Key1:=Range("B3"), order1:=xlAscending, _
Key2:=Range("G3"), order2:=xlDescending, _
Key3:=Range("K3"), order3:=xlDescending, _
header:=xlYes

Range("A3:S3").Insert Shift:=xlDown
Application.EnableEvents = True
End Sub

Thanks!

"Gord Dibben" wrote:

Check out help on the INDIRECT function.

Or have a look at Debra Dalgleish's site for all you need to know about INDIRECT
with examples.

http://www.contextures.on.ca/xlFunctions05.html


Gord Dibben MS Excel MVP

On Mon, 15 Jan 2007 09:31:01 -0800, Orangepegs
<Orangepegs@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:

Hi all!

Here's what I'm trying to do: I have an autosort function from *** two
showing scores from my employees. The scores and order of the employees
changes daily, so I'm trying to link the top five into a seperate *** that
will continuously update as the order from the larger *** updates.

The problems: the basic "link" function does not link the cell G5 from ***
2 to B6 in *** one, rather, it links the contents of G5 in *** 2 to B6 in
*** one. So when the sort function of *** 2 kicks in, the contents in
cell G5 could slide down to G6, but what's now in G5 is not what's linked to
*** 1, rather, it's what is now in G6.

Is there another formula or coding I should be using?

Thanks,

Orangepegs


.

Quantcast