Function Problems

S

sherriq

I need to display the four top sales and reference the name of the sheet the
information came from in the cell beside it. I can get the largest number,
but from there all I get is errors. Any help?
 
D

Dav

You will need to give a little more information, we have not got enough
to go on, What is stored on the Sheets etc Is it any cell or the same
cell on each sheet? etc

Regards

Dav
 
S

sherriq

Each sheet is for a different salesperson and contains all of their sales
for the year. It has a running total on the bottom of each page for that
salesperson. I used the same cell number (B43) on each sheet. What I need
to do is have it post the 4 top salespeople and their names. Each sheet is
named with the salesperson's name. Hope this is enough information.

Sherri
 
D

Dav

You could copy the following macro into a module and it would tell yo
in cells e5:f8, you can change the range by changing that bit at th
bottom of the macro

I have also assummed that the results sheet is the first sheet in you
workbook and have created a second sheet that remains hidden to stor
the interim data

Sub Top4()
Sheets(2).Visible = True
Sheets(2).Select
Cells.Select
Selection.ClearContents
For x = 3 To Sheets.Count 'for all the other sheets (gun crime sheet
go from the first sheet (now sheet2) to the last
Cells(x, 3).Select 'go to cell (2,3)=c2 then c
then c4 etc
Selection.Value = UCase(Sheets(x).Name) 'put the name of the worksee
in that cell
Cells(x, 4).Select 'go to cell d2, then d3 etc
Selection.Value = Sheets(x).Range("b43").Value
Range("C3").Select
Selection.Sort Key1:=Range("D3"), Order1:=xlDescending
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Next x
Sheets(1).Select

Range("E5").Select
ActiveCell.FormulaR1C1 = "=Data!R[-2]C[-2]"
Range("E5").Select
Selection.AutoFill Destination:=Range("E5:E8")
Type:=xlFillDefault
Range("E5:E8").Select
Selection.AutoFill Destination:=Range("E5:F8")
Type:=xlFillDefault
Range("E5:F8").Select
Sheets(2).Visible = False
End Sub


Regards

Da
 
Top