I am trying to group data for a specific event and have not been successful,
so I am hoping someone can help.
ID Date Value
174 12/3/2007 102
174 12/3/2007 909
174 12/11/2007 405
174 12/11/2007 701
206 12/6/2007 1001
173 12/3/2007 500
I want to group by ID, the MAX Date for that ID and the Max Value for the ID
& Max Date
The desired result would be:
ID Date Value
174 12/11/2007 701
206 12/6/...Extend Rows and related formula downwards using functions not macros
I have a simple worksheet to amortise a loan. I would like to use a
formula to extend the rows downwards to the number of payments (Number
of years x Number of payments per year) using a formula.
For example, if there were 26 payments per year over 1 year, there
should be 26 rows. Now if I change the number of years to 2 years, two
things should happen:
1. The number of rows should expand to 52 from 26
2. The sum of the interest paid should include these extra rows.i.e
=Sum(rownumber 1 to row number 52) instead of Sum (rownumber 1 to
Currently I am using a simple formula alon...Highlighting Rows #4
Is there a way to automatically highlight the entire row in color so that
when you are entering data into a large spreadsheet you know which row/cell
you are in?
see your other post
"Kim" <Kim@discussions.microsoft.com> schrieb im Newsbeitrag
> Is there a way to automatically highlight the entire row in color so
> when you are entering data into a large spreadsheet you know which
> you are in?
...Choosing a value from a row with more than one condition
I have a table with some duplicate values in various fields. I need to
extract a value from a certain row that answers to two different creterias:
Name Skill Score Active
1. John Craft 2 no
2. John Track 3 no
3. Dana Craft 7 yes
4. Dana Track 2 yes
In the example above I am trying to get the Score value where Skill =
"Craft" and Active = "yes"
VLOOKUP is obviously not good enough as it gets me the first Craft it
encounters without considering a second creteria.
How do I select from a table w...Group Footer at Bottom of Last Page of Group
I would like for a group footer to appear at the bottom of the last page
of a group -- either hide the page footer on pages if it is not the last
page of the group or have the group footer print at the bottom of the page.
I have used the instructions to reset the page number and the total page
count for each group. (http://support.microsoft.com/kb/841779/en-us) I
was hoping to be able to set it where if the page equals the total pages
of the group, it will make the page footer visible. For some reason, if
there is more than one page, it doesn't work properly.
I have also used the i...query based distribution group
running Exchange 2007 sp2, I need to modify an existing query based DL so it
finds mailboxes and if the account has two different email address stamped
on it. For example if the mailbox has test1.com and test2.com and it's a
mailbox then make it a member of the query based DL
Thanks for any help
If it has those two e-mail addresses? That should return just one
Or do you mean a recipient with any two proxy addresses? I don't believe
that's possible with LDAP.
Or do you mean a mailbox-enabled user with proxy addresses in two domai...Creating Universal Groups with Distribution Group members?
We have a Windows 2003 Active Directory with Exchange
2003, and I don't really want to duplicate work, but I
think I may have to (ugh!).
Here is the deal. I need to create an All Managers
Universal Group with every group that has managers in
it. So, what I tried doing, was I created a Universal
Group and added the _All Managers DL into it -- but that
didn't seem to work, according to my boss who asked me to
create this UG for him. So, my question is, am I not
allowed to add DL members to a Universal Security Group?
And if now, is there any way that I can take...how do I chart columns, rows and sheets
I want to chart a set of colums and rows which vary from sheet to sheet.
Basically three axis.
How about two axis, with different series representing different values from
sheet to sheet?
This page describes a bit about using data from different sheets in a chart:
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
"yorkieshome" <email@example.com> wrote in message
news:E51CB657-0938-4845-80C5-DD7CA8ABD81D@microsof...Pivot Table Repeats Names in multiple rows
I have data on hours worked for about 70 employees (Names are rows) for the
last year (Months are columns). For about 15 of those names, the Pivot Table
is not consolidating all the data for that employee. Rather, it repeats the
name for 2 or more rows as if those names were spelled in various ways. The
hours data for those affected employees are also broken out among the various
rows. The source data originally had this problem, but I fixed all the
misspellings. By filtering the names field in my source data, I have
confirmed that there is now only one version of each name. Why c...Distribution Group list
I have a problem with the distribution group list. Here
it goes: I removed and deleted the account from this
group list but whenever I send a task, it always tries to
reach this deleted account and give me a undeliverable
error message. Please help. Thanks
Restart Exchange services.
> I have a problem with the distribution group list. Here
> it goes: I removed and deleted the account from this
> group list but whenever I send a task, it always tries to
> reach this deleted account and give me a undeliverable
> error message. Please help. Thanks
...How to number rows after data has been filtered?
I want to know continously give a serial number skipping in between some rows
that are filtered.
In excel if we try Fill series, it is not working on filtered rows.
Is there any way out? Please help.
I think I'd sort the data to group those visible cells. Then fill those empty
cells and resort the data to its original order.
I'd have a column that was essentially a row number column.
show all the rows first.
Insert a new column A.
put 1 in A2 and 2 in A3
select a2:a3 and drag down your data.
Filter your data to see the blanks you want to fill.
select that range of visible cell...maximum rows limitation in an access 97 table
we have an access97 db which has 32405 rows, is there such
a limit of 32700 rows in access 97 and if there is, can
this be rosolved by upgrading to Access 2000 or higher.
> is there such
> a limit of 32700 rows in access 97
No, the limit is that the database cannot be larger than 1GB. Check out
other specifications in Access Help, using "specifications" as a search
criterin in the Search Wizard.
"GM" <firstname.lastname@example.org> wrote in message
>...Data, Group got error message Cannot shift object off sheet
I created a command grouping several columns together by going to Data, Group.
When I go to click on the "-" sign to group. It gives me an error
message;"Cannot shift object off sheet". Why? and how can I fix it.
Maybe you have some objects that get rearranged when you hide the columns using
your grouping symbols.
This may have some suggestions that work for you. It's kind of a similar
XL: "Cannot Shift Objects Off Sheet" Error Hiding Columns
Remember to look for comments and merged ...Autofilter
When I am using Excel's Autofilter, often the statusbar shows the number of
selected rows as part of the total number of rows.
Often, but not allways.
How is this, and what can I do about it ?
When your list has many formulas, or if a cell in the list is changed
after a filter has been applied, the Status Bar may show "Filter Mode"
instead of a count of the visible records. You can use the Subtotal
function to count the visible rows.
The following MSKB article has information:
The AutoFilter status bar displays the "Filter Mode" me...Local Data Groups
Does anyone know if its possible to create local data groups for offline
clients and somehow push the groups out to all users, rather than going to
each outlook laptop client?
You can deploy the SFO client using group policies. When the user next logs
onto the domain the SFO client will be installed
Microsoft CRM MVP
"Jo Savidge" <JoSavidge@discussions.microsoft.com> wrote in message
news:CAE65DA4-C2E3-4F50-A78B-39C98940A129@microsoft.c...graphing by group
I have 10 individuals in 5 groups.
I would like to graph the individuals but keeping them
within their groups. When I try to graph it ends up as 10
I would also like to make each group of individuals a
In Step 2 of Chart Wizard make sure you have Series by Rows or Series by
Columns correctly selected.
If you groups are in different columns us Series by Columns other wise use
Series by Rows
remove CAPS in e-mail address
"Tim Pasma" <email@example.com> wrote in m...Passing option group value to vba function
I'm sure I've done this before but can't for the life of me figure it
out now, and hours of research online haven't helped. I'm simply
trying to pass an option group value from a form to a vba function.
I'd like the user to choose option1 or option2 and when they click a
button, the function will run an if statement like "if
I've got 2 options named option1 and option2 and a frame called
frame20, what else do I need? I've also tried bounding the selection
to a textbox, but keep getting errors. Any help on the process?
Tha...managing distribution groups
I am running exchange 2003 and I would like to know is there a way and I can
have a regular user maintain my distribtion groups. If so how would I go
Thanks so much
You can grant permissions at the disty group level to allow ordinary members
to add and remove users. Be careful to only do disty groups as security
groups changed by users in outlook is a touch insecure.
"Mike B" <MikeB@discussions.microsoft.com> wrote in message
> Hello all,
> I am running exchange 2003 and...Excel should handle more than 65,536 rows.
We deal with large amounts of data and the current row limit of 65,536
repeatedly hinders us. This has been the limit for a long time and I don't
understand why it hasn't been increased or eliminated. With today's
computers it seems Excel should be able to handle much more. At the very
least make it an overall number of cells limit, not an X by Y limit.
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not s...DSum by group
I have a textbox with a DSum function, but would like it to query only within
each group. It seems that most other functions (e.g Avg) automatically
calculate by group, but DSum doesn't seem to be.
The text box is within the Group Footer, and I'd like it to return a
different value below each group.
"RussG" <RussG@discussions.microsoft.com> wrote in message
>I have a textbox with a DSum function, but would like it to query only
>within...Changing the row reference in a link
I would like to be able to change the row that a cell references by entering a number in a cell. I have a sheet that automatically updates various data (such as stock prices). I then have another sheet that has a standardized report format. I would like to have a cell linked to the stock price (such as =Data!F19) but have a cell so that I could change the row link from 19 to another number by just entering that number in a cell on the standardized report sheet (something like =Data!F(A2)). I would like to avoid a macro if possible..
...Autoadjust range declaration coding when inserting rows
Is there a way to auto-adjust the range settings for when you add/delete rows
to an Excel sheet? I'm trying to set up an easy way for end-users to
show/hide blocks of information with a button, and I'm using the code below
for each button:
Private Sub CommandButton1_Click()
Dim myRng As Range
Set myRng = Me.Range("7:12")
myRng.EntireRow.Hidden = Not (myRng(1).EntireRow.Hidden)
Now, the problem is that these blocks of info will have extra lines
inserted, which tends to break all of the coding. Is there some sort of
adjustment to the coding that I ca...Re: Protecting worksheet with grouped cells
I have a worksheet which has rows as well as cloumns
grouped. After protecting the worksheet I am unable to
expand and collaspe my groups.
error:"You cannot use this command on a protected sheet..."
Is there a work-around/solution to this problem?
You can only enable outlines and have worksheet protection through a macro:
.EnableOutlining = True
The EnableOutlining property (and UserInterfaceOnly) is not saved with the
workbook unfortunately, so this code would have to be run ...Grouping and grouping within groups
I really need help, and help fast, so thank you ahead of time! I nee
to know how to group and group within a group. I have been able to d
a group, but once I group within the group and try to group anothe
group withing that group it combines them. I know I can put a spac
between the information so it doesn't combine them but that doesn'
seem to be the right way to do things. For instance I have thre
different area codes and I want to group them, then within those group
I have five different colors of houses in that area code. I want t
group the different colors within their area ...Sum of row untill you reach a certain point (2)
I have a twist on a problem that was answered earlier by the very intelligent
I want to add columns A2 through P2 until it equals the total in cell A1
Once the total would be met it would zero out the remainder of the cells.
Cell A1 = 30
Cell A2 = 14
Cell B2 = 15
Cell C2 = 16
Cell D2 = 17 etc. (All the way to P2 which equals 29)
In the end I need:
Cell A3 to equal 14
Cell B3 to equal 15, But we still need to add as we have not reached
Cell C3 to equal 1, Because we only need 1 more to total to 30
Cells D3 to P3 to equal 0 as we have equaled or matched Cell A1 ...