Formulas to (a) pull month, year and (b) sum within date range?
I have a list of dates in column A, and numbers down cols B-E. Off to
the right, I need a table that lists the month/year and the totals for
each of the columns. It needs to be a separate table, rather than a
pivot table, so it can be copied-and-pasted by others.
What I _think_ I need is:
-- a formula in H2:H10 (maybe more) that will look down the full range
of dates (call it A2:A100) and put one month/year per row
>> H2 = 02/09
H3 = 03/09
H4 = 04/09
-- a formula in I2 that will sum all entries in B2:B100 with the month
and year of H2
I think I could get the secon...setting form record source when form is opened
I have a question about setting the record source of a form at the time the
form is opened.
Background: I need to clean up and modify an application. The application
currently only has one table and one template form for data entry. However a
separate copy of a selection query and a separate copy of the data entry form
have been created for each analyst in the office. The only difference
between the queries is the CurrentAnalyst criteria is set to one person’s
name. The only difference between the forms is the record source is set to a
different selection query. Currently the analysts o...If cell value is greater than another cell value, clear contents.
Okay, hopefully this is the last question today!
I've found the below formula in one of the posts here, but my brain has
completely failed on me and I'm not sure how to adapt it to my requirements.
I have a value which is entered by the user after a prompt, that populates
in cell G4 in a sheet called 'Lookup'. For arguments sake, the current value
I have another worksheet, 'Data', which has a column populated with dates
(say column B). I'd like a macro that looks in column B on the data sheet,
and clears out any dates which are gr...How do you ensure that null-valued elements are not serialized?
I am using complex types in order to support serialization/deserialization
of floating point numbers, since floating points can't be null.
I've seen how to suppress attributes that are "not specified", such as
having a float member called Value, and a bool member called ValueSpecified.
This instructs the XML Serializer to omit that attribute altogether if it
wasn't "Specified". But how can I tell it to omit the XML element
Here's the problem:
I deserialize an object that looks like this:
<name>Joe<...Combo Box default value
I am trying to set a default value for a combo box that is a static list
with three items in it. Everything I have found googling has related to
setting the default value in the controls properties using this format:
Using this suggestion hasn't worked so far and I am sure it is a simple
Sorry, it is on a form. I want the default value of the combo box on the
form to be the first item in the list.
"Ofer Cohen" <OferCohen@discussions.microsoft.com> wrote in message
news:EEEFDE47-409A-4D08-B426-0374F...Formula help *
Office 2007 I am trying to display in a cell the last value in a selected
column. Numbers are added daily to this column but I need this specific cell
to show the last entry at all times - any ideas or help would be appreciated
Thanks in advance Bryan
Sun, 10 Feb 2008 20:53:28 +0900 from JohnWA <j_ustforfun@hotmail>:
> Office 2007 I am trying to display in a cell the last value in a selected
> column. Numbers are added daily to this column but I need this specific cell
> to show the last entry at all times - any ideas or help would be appreciated
=OFFSET($A$1,COUNTA($...Best Practice for Set up your internet address and 2 different domains
I want to make sure i get this right the first time. I am setting up
an SBS 2008 server and am to the point of setting up the domain. I
have 2 - but, both are for different things.
name1.com = remote access (don't need for e-mail, just RWW)
name2.com = e-mail for company (don't need for RWW, just e-mail)
It is my understanding that the "Set up your internet address wizard"
will setup exchange (which i will need for name2.com) as well as
remote access (which is name1.com)...
Another facet is this: i'm not using Exchange completely for e-mail, I
am usign th...Insert spaces between pasted values #2
Is there a way to insert spaces between values that are pasted? For example, i have in rows 1,2,3 values a,b,c and i want to paste them into another list in rows 1,3,5 respectively (with a single-row space between pasted values).
I presume you have more than just 3 rows. A general approach would be
to use a helper column next to your data and to fill a simple sequence
down that column for as much data as you have. Then copy all the
numbers that make up that sequence and paste them immediately below
the sequence in the same column, so that if you have 200 numbers for
example the...How to set default zoom scale for IE8?
Does anyone have any suggestions on how to set default zoom scale for IE8?
I am using XP with SP3, the default zoom scale is 100%, and I would like to
change it into 135% as default.
Does anyone have any suggestions on how to do it?
Thanks in advance for any suggestions
Click the "Change zoom level" arrow at the bottom right corner in IE8, click
"Custom" and set it to 135%.
Click Tools, Internet Options, Advanced. Uncheck "Reset zoom level for new
windows and tabs".
Ramesh Srinivasan, Microsoft MVP [Windows Desktop ...Excel formulas #9
I need a formula to compute a final grade for a class when there are assignments missing. I also need to figure out what my target grade is using goalseek?? I need an if formula that displays TRUE if you are currently passing your class and FALSE if you are not. Any help would be appreciated.
To help you we would probably need a bit more information
from you -
1)how will you calculate the final grade? (EG Average of
2)What grade/percentage dertines the pass grade
>I need a formula to compute a final grade for ...= if formula needed
good day all, i am trying to get this formual ie: =if(q80="closed",q1=now())i
cant get it right. i have tried various variations of the formula.any help is
appreciated. thx wyn
in you example is the cell you want updated "Q1"?
If so than it should read
> good day all, i am trying to get this formual ie: =if(q80="closed",q1=now())i
> cant get it right. i have tried various variations of the formula.any help is
> appreciated. thx wyn
In that formula yo...User Defined Function vs. Named formula
I have used Named Formulas and am now learning about User Defined
Functions. One thing I found with named formulas is that the naming
conventions around global/local selections. Reading about UDFs makes
me think they might be a better option for a lot of my work.
Any insight as to When to use one over the other will be appreciated.
IMO they are different beasts.
A named formula would be used to shorten formulae by including a name rather
than the sub-formula (especially where it is repeated), and to improve
readability/maintainability, as the name would/should be meanin...Simple If, Then formula for excel
So I am fairly weak when it comes to Excel formulas, so I'm sure what I
require is really simple, I just have no idea how to formulate it.
Basically, I have a column of values, some of which are negative values and
some are positive, but they are changing from month to month (i.e. accruals
and prepayments). I need to add up all the negative values from the column in
a new cell, and all the positive values in another.
So it will be something like If amount>0 then add to cell, else add to other
So there you have it, really simple I just have no idea what to do. So i...Excel Formula #8
I am trying to format spreadsheet. Need 3 counts. 1st
count is for 90% and above =COUNTIF(H5:M16,">89.99").
Second count is for Under 80% =COUNTIF
(H5:M16,"<79.99") . Can anyone help me with a formula for
3rd count, between 80% and 89.98? I can't seem to
formulate it. Thanks in advance
=COUNT(H5:M16) - COUNTIF(H5:M16,">89.99") - COUNTIF(H5:M16,"<79.99")
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
"Barb" <...converting text values to number & decimal values..
I have any array which read a csv file and I am using split and populate
these fields into my table..
Now I want to convert my text fields to number and decimels respectively.
I tried the following functions but this works fine if they are number and
fails it was text value.
TESTID = CInt(sampleid) 'CInt(myarray(1))Convert to number
CT_num = CDec(ct_val)
Message posted via http://www.accessmonster.com
Test to see if they are numeric first.
If IsNumeric(sampleid) Then
TESTID = CInt(sampleid)
...How do I change the values in loan amortization
I am wanting to use the loan amortization spread sheet, and was wondering how
you change the values as I want to change Loan period in Years to calculate
Loan period in Months?? I have tried several things but not having a great
knowledge on using Excel im failing miserably! PLEASE HELP!!! :O)
a combination of dividing by 12 and multiplying by 12. play with it
"ROXYAWORLD" <ROXYAWORLD@discussions.microsoft.com> wrote in message
>I am wan...Is there a maximum number of values entered into a pick list?
For our project we need to add a very large amount of values into a pick
list. 1839 values to be precise. When and existing entity that contains the
pick list is opened we get an error. The event view contains the following
Error: 17805, Severity: 20, State: 3
Invalid buffer received from client.
The strange thing is that when we create a new record we do not receive and
error. We receive the error when attempting to open up a saved record.
Has anyone seen this issue?
...Perspective setting won't save in a 3-D column chart
I have a 3-D column chart that has the perspective setting as 20, the
elevation as 24 degrees and the rotation as 198 degrees. I change the
settings, close the dialog box and click save. Yet the perspective setting
resets itself to 15 every time I open the file. I am using Excel 2007. Any
I have been able to duplicate your problem, I suggest this is a bug,
although there might be some reason for this behavior, I can't think of any.
It's probably not worth it but I suppose you could attach a macro to the
Open_Workbook event and reset it to...SQL OPtions when setting up new user
In version 7.5, there was options for updating the SQL server with user
information, (checkboxes) I cant find that in version 9. What is happening
is we add users to PDK, and have that program update the SQL database with
the user information.
Now when I try and add a user to Great Plains, I get the message that the
user is already a user in the database. Where can you turn the option off to
update the SQL database and just add the user to Dynamics?
Same question. Please advise!
> In version 7.5, there was options for updating the SQL...changing categories settings
Why is that when I go to set up a category on the category list, I can't find
the option of "special - category (transfer to or credit card payment)" in
Why can't I have the credit payment category on my expenses report?
In microsoft.public.money, suzana wrote:
>Why is that when I go to set up a category on the category list, I can't find
>the option of "special - category (transfer to or credit card payment)" in
I am guessing you would want to set
Tools->Settings->AccountSettings->DisplayAllAccount...How to set Outlook files for backup using the Backup Tool?
Using the Personal Folders Backup Tool in Outlook 2007 using Vista, I
highlight the top level folder in my "All Mail Items" list (labelled as
Personal Folders) and click File/Backup but under the direction "Save backup
copies of these personal folders files" the dialog box says "(No files set
for backup)." When I click on "Options," the next dialog box shows one
subfolder name with a checkbox to mark if you wanted to back up its files,
but not any others. There are a lot of other subfolders whose files I want
to back up, but they are not ...In Excel how can I assign values to a Yes or No question with IF
I am building a spreadsheeet, where If a cell has a "Yes" in it is is work
..75 and a "no" equals 0. I know a IF statement will help, but I don't know
I also want Excel to caculate from an inputted date how many moths ago it
Can anyone help?
is all that you need
"craigscoop" <firstname.lastname@example.org> wrote in message
> I am building a spreadsheeet, where If a cell has a "Yes" in it is is w...Setting length of all cells in a column
I have a column in a spreadsheet that holds bank account
numbers. Unfortunately, although a bank account number
has to be 8 characters long (e.g. 00012345), some of the
leading zeros have been dropped, meaning that the cell >
only contains, for example, 12345.
Some fields only have one leading zero, so the field has
been dropped to 7 characters long, whilst some have no
leading zero so are already the required 8 characters
There are 8000 lines on this spreadsheet so changing the
lines manually is not feasible. Any idea how I could
quickly change those fields that are not alr...checking duplicate values on data entry
In my table design,Because I need to accept No value , I can not use NO
duplicate index for my field , but I shouldn't let duplicate values in it. to
develop the need I check the value in the data entry form using "Find first"
of the record set. On my own computer it is OK and no duplicate value can be
entered. but users CAN add duplcate value.(mine is office xp but some use
office2003). what should I check? and if there are better solutions please
let me know. tnx in advance.
The version of Access should make no difference. How are you using FindFirst
a...Set reminder when importing into Outlook
Does anybody know how to set the reminder on when importing meetings from
Excel to Outlook? What do I need to put in the Excel column to make Outlook
read that I want it to turn on?