link cells
 

Hi all!

Here's what I'm trying to do: I have an autosort function from sheet 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 sheet that
will continuously update as the order from the larger sheet updates.

The problems: the basic "link" function does not link the cell G5 from sheet
2 to B6 in sheet one, rather, it links the contents of G5 in sheet 2 to B6 in
sheet one. So when the sort function of sheet 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
sheet 1, rather, it's what is now in G6.

Is there another formula or coding I should be using?

Thanks,

Orangepegs

link cells
 

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
wrote:

Hi all!

Here's what I'm trying to do: I have an autosort function from sheet 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 sheet that
will continuously update as the order from the larger sheet updates.

The problems: the basic "link" function does not link the cell G5 from sheet
2 to B6 in sheet one, rather, it links the contents of G5 in sheet 2 to B6 in
sheet one. So when the sort function of sheet 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
sheet 1, rather, it's what is now in G6.

Is there another formula or coding I should be using?

Thanks,

Orangepegs

link cells
 

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
spreadsheet, 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
wrote:

Hi all!

Here's what I'm trying to do: I have an autosort function from sheet 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 sheet that
will continuously update as the order from the larger sheet updates.

The problems: the basic "link" function does not link the cell G5 from sheet
2 to B6 in sheet one, rather, it links the contents of G5 in sheet 2 to B6 in
sheet one. So when the sort function of sheet 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
sheet 1, rather, it's what is now in G6.

Is there another formula or coding I should be using?

Thanks,

Orangepegs