Identifying the top five values in multiple groups
I've got a spreadsheet of pay information for about eight hundred people.
Each person is on one of eight salary scales
I'd like to create a new worksheet that shows the details of just the 5
highest paid people in each scale (name, dept, salary, etc.) - and also the
five lowest. Ideally, I'd like also to be able to vary that number - eg the
top ten, the highest, etc..
Can someone help?
Suppose you have data in Sheet1 in the below format
Col A Col B Col C
Name Scale Salary
a1 1 101
a2 1 102
a3 1 103
a4 2 104
In Sheet2 cel...How to remove Cartages returns?
How do you remove Cartages returns when import excel to Access?
On Wed, 24 Oct 2007 08:18:04 -0700, Hortp01 wrote:
> How do you remove Cartages returns when import excel to Access?
And replace it with what?
Please respond only to this newsgroup.
I do not reply to personal e-mail
With a space.
On Wed, 24 Oct 2007 09:11:03 -0700, Hortp01 wrote:
> With a space.
It's polite to include the relevant portion of any previous message in
a new message. Without a reference to your previous message and my
reply, any other reader will h...Compare address to a range
I have this code in an event macro:
If Target.Address = "$BC$3" Or Target.Address = "$BC$4" Or Target.Address =
"$BC$5" Or Target.Address = "$BC$6" Or Target.Address = "$BC$7" Then
Is there a more compact way to write that statement? Thanks for any help on
Look at this:
Set isect = Intersect(Target, Range("BC3:BC7"))
If Not isect Is Nothing Then
If Not Intersect(Target, Range("BC3:BC7")) Is Nothing Then
"dhstein" <email@example.com...Table relationships and lookups
Hi guys, I may be a little over my head, I've had some experience in
creating simple access db's. however this one will be extremely
complicated as far as I can tell. Some backround info - i've got an
excel spreadsheet currently that i would like to convert to Access.
The spreadsheet does multiple lookups and calucations.
This is for a Soccer club that i run to maintain roster information,
dollars, scheduling and stats. I'm currently working on the
scheduling pience. Here's what I have so far.
Club - Lists the teams in the club, home field name and ...Dynamic Range #2
I am working on some VB for spreadsheet. Is there a way to make the
code to dynamically recognize the last cell with data in it?
I currently have my code hard coded (as below) and I wanted to make
the "G222" part dynamic to find the last cellw ith data in it.
Thank you very much in advance.
assumes a is your LONG row. Don't forget the . s
dim lr as long
.Range("A3:G" & lr).Sort
D...return receipt #4
OL 2002 SP3
Win XP HE SP1
Follow-up to: microsoft.public.outlook
If I send an email requesting a return receipt, when the email is
clicked or opened, it will pop-up on recipient's work station asking if
he wants to send return receipt.
Let's say he says no. But obviously reads the email anyway.
When I get the return receipt back, it will say "email was deleted
without being read."
Can this mean that the above happened, i.e. recipient clicked No on
sending return receipt or does it mean that recipient definitely deleted
the email based on seeing its header in his inbox...Problem with Null value elimintation criteria
Access 2007 on Vista.
I'm building a simple append query to add missing records to a table. It
examines a list of entries, identifies which are not in the destination
table, and adds them. Simple thus far.
The problem comes when I add a criteria to the source side to ensure no
blank entries are appended.
Here's the SQL I'm trying to use:
INSERT INTO tblAgents ( AgentID, AgentName )
SELECT qryAgentsImport.RecAgentID, First(qryAgentsImport.RecAgentName)
FROM tblAgents RIGHT JOIN qryAgentsImport ON tblAgents.AgentID =
qryAgent...Invoice lookup by paid check number
I frequently have vendors call me asking for information about what invoices
were paid by a check number. Is ther an easy way to look this information up?
You could go to Inquiry>Purchasing>Transactions by Document, put your check
number in the 'from' and 'to' fields. Once the document is displayed in the
scrolling window zoom back on the 'Unapplied Amount' field. Viola! the
documents paid by the selected payment are listed. Unfortunately there isn't
a print icon on this inquiry window, but I think it's the information you
&quo...Handling #REF returned from GETPIVOTDATA
A GETPIVOTDATA function returns #REF, but when I change that formula to
=ISREF(GETPIVOTDATA... I get FALSE. I would have expected TRUE. Why the
This is part of the development of a formula to return a zero when
GETPIVOTDATA returns #REF.
will return true if you pass it an address:
(since A1 is an address)
I don't recall ever seeing this used on a worksheet. I bet that this may have
been useful in the older XLM style macros (just a guess).
Laurence Lombard wrote:
> A GETPIVOTDATA fu...Select range for function in a cell
Hi, is it possible to select create a formula in a cell. such as =SUM(
and have the cursor be between the () so the user can select th
appopriate range to enter? Or would this just be done separately.
(Since one would need to know the range and WHERE to put the formul
in..ie. what cell) similar to if you hit the autosum button but ther
are no numbers above or the left, it simply waits for the user t
select a range and then hits enter.
Message posted from http://www.ExcelForum.com
if I understood you correctly: No
> Hi, is it p...Lookup
Q102 Q202 Q302 Q402 Q103 Q203
How can I lookup the Q103 in the row above and then have
it pull the number to the right one cell (Q203)? thanks
If I understand correctly
where q103 is in a1
and q102-q203 is in a2-f2
>Q102 Q202 Q302 Q402 Q103 Q203
>How can I lookup the Q103 in the row above and then have
>it pull the number to the right one cell (Q203)? thanks
> Q102 Q202 Q302 Q402 Q...Entering an unbound form control value into a new record field.
Hello. I hope this (friendly and helpful) group will forgive a very noob
question, but I learned from my (long gone) self-teaching years with foxpro
that a little good advice can save a lot of grief. I've been setting up my
tables for first time Access project (2007 on Vista), and thinking about my
first form. It will have a couple subforms in it, and is intended for
browsing as well as entering a new record. My focus at moment is on a text
box, whose value will be derived (and therefore be unbound) from other bound
controls (an autonumbered integer (the PK), a user enter...Print Range Lines
Is there any way to turn off the dotted lines that show the print range area
I don't know why someone would want them unavailable, but a co-worker of
mine insists "they don't turn on half of the time anyway, so why use/show
them at all?"
We use Excel 2003.
Tools>options>view and under window options deselect page breaks
"Me" <Me@discussions.microsoft.com> wrote in message
> Is there any way to turn off the dotted lines that show ...A Lookup function does not work
Hi, This is my first posting.
I am using Exel 2000. I have 2 separate spreadsheets that have some
similar columns but not all of the data in the similar columns is the
What I want to do is take column A in spreadsheet#1 and find this same
value in Column B in Spreadsheet#2 and then insert into column 3 in
spreadsheet #1 a value from a different column in spreasheet #2 that
corresponds to the row in which the value was looked up in Column B in
What I am doing is comparing 2 different inventory files that have
stock codes in columns and quantities in another column, but n...sort dynamic range on active cell
is there anyway to sort a dynamic range (ie amount of columns and rows may
vary) based on the column of the active cell. to make things even more
complicated i would like to have a button that when you click it the data is
sorted ascending and if you click it again it is descending)
Name, age, class, height = col headings
if i click a in any cell in col b (age) then click button it sorts data
ascending, click again and its descending
If i then select a cell in col d (height) i can sort that col based on
active cell being in it
i hope there is a neat way ...current user returns admin
I would like to log in one of the databases the username of the person who is
making changes to a record in a database, but I noticed that access 2207
doesn't have user level security anymore and the currentuser method just
returns admin. Is there a way to do this?
On Jan 3, 10:16 am, icccapital <icccapi...@discussions.microsoft.com>
> I would like to log in one of the databases the username of the person who is
> making changes to a record in a database, but I noticed that access 2207
> doesn't have user level security anymore and the currentuser method ju...Help with LOOKUP function
This function is in a workbook with 2 sheets. It _almost_ works
perfectly. These "C" columns in two different sheets
'2005-2006'!C:C,'2004-2005'!C:C, contain names of people. The D column
in one of the sheets - '2004-2005'!D:D - contains a date associated
with the person's name from the C column of 2004-2005 sheet.
This formula is in the "D" column of Sheet 2005-2006.
The concept is for the formula to lookup the value (person's name) in
column C of 2005-2006 a...return cell reference in a table based upon given lookup criteria
Is there a way to return the cell reference, or column/row coordinates,
of a cell within an array or table by providing lookup criteria?
Perhaps something like this:
For a table of value in A1:E10
F1: (the value to find)
Note: Commit that array formula by holding down the [Ctrl][Shift] keys and
That formula returns the address of the 1st cell containing the value in F1,
or #VALUE! if there is no match.
Am I on the right track here?
"Travis" ...Selecting a range of cells based on the date.
Hi MS Office Help,
I would like to know how to choose a selection of cells based on what month
For instance, if the month was january, excel would allow me to select cells
A1-F1, which I could then use as a data source for a chart. If the month
became march or april, excel would allow me to select cells C1-H1, D1-I1
respectively, and so on. This will allow me to vary my chart automatically as
the month changes.
Help is greatly appreciated.
Use the Data - Autofilter -custom feature
or Data - filter - text filters - custom
depending on your version of...Colour fill within a range
I have a spreadsheet that i want people to be able to colour cells in using
I also only want them to colour specific cells. A3 to Z25
The sheet is protected ,and to enable them to colour cells i have a macro
which first unprotects- colours the active cell - reprotects.
is there any way that i can prevent people from colouring cells that they
are not supposed to.(e.g A1)
I Only want people to colour cells between Column A Row 3 and Column Z row
This is the macro:
On Error Resume Next
ThisWorkbook.Worksheets(&...Blank Repeated Values
I have a list in Column A that displays multiple data in an unfilled
manner. I have a list in Column B that displays multiple data in a
filled manner. How do I autofill the data points in Column A?
Example: A1=1 A2:A9=(blank) A10=2 A11:A19=(blank)
I do not want to manually autofill each data break because there are
30,000+ rows of data (A1:A30000) with data points starting at 1(A1) to
The format looks like a finished pivot table. It looks like I am trying
to copy a finished pivot table and pasting value to another sheet, then
running a pivo...InternetReadFile returns additional characters at the end when reading several bytes?
I have the following problem: I am using wininet's InternetReadFile to
read an XML file from a server (find the code snippet below). I have
set a BLOCK_SIZE of 512 bytes to read with each InternetReadFile call.
Sometimes the XML which is read into my lpBuffer looks wellformed, but
sometimes I can see at the end of the file some additional characters,
e.g. if my XML would look correctly like this:
I sometimes see this:
So it looks like some memory problem and that the buffer is not f...Advanced Lookups
Is there any way to make an advanced lookup the default lookup? so you don't
have to always choose that option when doing a lookup?
Thanks for any help.
Advanced lookups ARE the default unless you've done something to make it now
so. There isn't any way to "choose" the option when doing a lookup that I
know of unless you have some type of customization (easy to do) that would
give the user an option.
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tracey D" <...Calling employee lookup from button through VBA code
Can anyone show me how to call an existing GP employee lookup from a button
of a modified form through VBA code.
Thanks in advance.
If I'm understanding the question - you need to add the lookup button to
your project and make sure your project provides that it runs on the modified
> Dear All,
> Can anyone show me how to call an existing GP employee lookup from a button
> of a modified form through VBA code.
> Thanks in advance.
As per...Call & Place Graphic Based on Cell Value?
Is there any way to call & place a graphic image based upon a cell value?
Maybe you can look at J.E. McGimpsey's page:
> Is there any way to call & place a graphic image based upon a cell value?