FRx CALC row
I'm trying to construct an FRx report that calculates a fixed percentage of a
GL Acct, where the percentage changes depending on the base period.
For example, let's say I want the percentage to be 30% for the first 6
months, and 50% for the last 6 months. How can I get the MTD amount for the
base period, as well as the YTD amount, to be correct?
If we assume that an expense account always has $100 for each period, if the
base period is 7, the MTD amount is $50 (50% of $100), and the YTD is $230 (6
periods of $30 plus 1 period of $50).
The report will only have a single MTD colu...How can I make Grouping with summary rows ABOVE detail the default
In Excel 2002 on the Menu: Data > Group and Outline > Settings
I'd like to make the setting "Summary rows below detail" unchecked as the
default. I have never wanted it with the summary row below the detail
therefore I have to go into this EVERYTIME I use grouping. Is there a way I
can make it unchecked as the default?
> Is there a way I can make it
> unchecked as the default?
Can't say. An option would be to use code to run it (stored in personal.xls
& assigned to a custom toolbar button) ;
Application.Dialogs(xlDialogSubtotalCreate).Show , arg6:=Fal...how to count email bytes
I want to measure the amount of emails flowing to our system in one
With the ESM, I am able to view emails depending on date and the ESM
display the size of every email. Is there a possibility to copy the
query result in a text file? Or is there another way to get the
quantity of all emails per day?
Thanks in advance,
Have a look at some of the Perfmon counters for SMTP (Messages Received
total, Messages Sent total, etc). They ought to be able to get you the
rough statistics you are looking for. The bad news here is that I believe
in order to reset the perfmon counte...Lookup and count?
I can't figure out how to do something.
I have a list of names on one sheet and I want to lookup another sheet,
compare each name with a list of names and when a match is found, check the
number of tasks that person has done (which is stored in an adjacent cell).
I don't know how to do this. Any ideas?
Got it. SUMIF.
"Bobby" <email@example.com> wrote in message
>I can't figure out how to do something.
> I have a list of names on one sheet and I want to lookup another sheet,
> compare...Summing rows
I've got Excel 97. It's easy to arrange summation of data in a column of
cells. You put in a result cell the summation formula for every column you
want to sum. You can use the same formula for summation in rows, but do you
have laboriously enter, one cell at a time, the regular summation formula.
If I had 200 rows, I'd have to enter the required formula 200 times. Cannot
I just highlight the column which contains the summations and do something,
so that each cell in the column will end up containing the row summation?
try something like the following in colu...Default Row Height in Excel 2003
This may have been covered before - and apologies if so - but I can't change
the default row height from 12.75 to 15.00 each time I open excel 2003. I
have tried saving as book1.xlt , book.xlt and even renaming the original
book1.xlt to bookold.xlt but still when I open up excel the default row
height remains 12.75
Would really appreciate if someone could either let me know how to do this
or if version 2003 won't let you! I know I did it in version 97
Where are you saving BOOK.XLT?
It must be saved into your XLSTART folder, usually at
C:\Documents and Settings\username...Copy & paste row in anther worksheet
how to copy & paste and Cut & paste data row from one worksheet or another
The requirement is I need to create a new worksheet first, and the copy &
paste or cut & paste a data row from one exist worksheet to the newly
because i am a novice in writing VBA in Excel , pls help
The easiest way to learn this kind of stuff is to record a macro when you do it
In fact, record two while you try both options.
I like to add the worksheet first, then do the cut/copy, then paste.
Sometimes, there are things that make xl lose the the cut/...How to count the number of text?
There is a list of text from cell A1 to A100, I would like to count the
number of "Mary" within this range, each cell may contain a senstance, such
as Mary goes to school by bus, or John meets Mary in Library ... etc. So far,
the counter for Mary is 2.
Does anyone have any suggestions on how to do it?
Thanks in advance for any suggestions
OR with the query string in cell B1
=COUNTIF(A1:A100,"*" & B1 & "*")
Jacob (MVP - Excel)
> There is a list of text ...hiding a row in a spreadsheet if calculated value = 0
I am working on invoices for my employer. Each row in the spreadsheet has a
calculated value. When the calculated value in the row = 0, then I am to
'hide' the row. If the calculated value is not =0, (could be positive or
negative), then I am to display or 'unhide' the row. Does anyone know how to
do this? Macros and VBA responses are ok. :)
Select the cells with the values (including the header row), then use Data / Filters....
AutoFilter. Select the dropdown on the header cell, then choose Custom, Does Not Equa...How do I show two rows of worksheet tabs instead of one?
I have about 30 worksheets in an Excel Workbook. How can I show more than
one row of worksheet tabs on the screen at once without scrolling?
Not possible Rob
But maybe you like the option to right click on the
arrows on the left of the first sheet tab
Regards Ron de Bruin
"RobtJWitt" <RobtJWitt@discussions.microsoft.com> wrote in message news:046F7DAD-E770-4961-8BF9-A3211950A4B8@microsoft.com...
>I have about 30 worksheets in an Excel Workbook. How can I show more than
> one row of worksheet tabs on the screen at once without scrolling...Go to the last-new row in a datasheet view
What is the code to go to the last row where is added a new record
when you type something?
I tryed adding a record to the form recordset but I don't want to
leave that record empty, I just want it to be ready to load data into.
Greetings from Paraguay.
Claudio Bogado Pompa.
Use the GotoRecord method. It is explained in VBA Help. If you are using a
macro, then it is the GotoRecord action, also in VBA Help.
Dave Hargis, Microsoft Access MVP
"Claudio Bogado Pompa" wrote:
> What is the code to go to the last row where is added a new record
> when you type something...Too many rows displaying on spreadsheet
Operating System: Mac OS X 10.6 (Snow Leopard)
I have a spreadsheet that has roughly 1,000 rows by 15 columns. HOWEVER, rather than the spreadsheet displaying a few more hundred below that, it is displaying 10,000's more rows. It is a bother rather than a real problem, but i can't use the scroll bar on the right. Any way to reduce the number of empty rows that are displaying? Thanks
The number of rows is fixed @ 1,048,576 but you can select & hide the ones
you don't want to have displayed using the Format> Row> Hide command.
The unanswered quest...Count Text Values
I have a column which contains text values whichare either yes /no. How can
I get a totals column at the bottom whichcounts the number of "Yes" values.
I'm fine if the cell contents are actual numbers but how do I do it if they
Win XP, Office 2007
"Andy Roberts" <firstname.lastname@example.org> wrote in message
>I have a column which contains text...VB Code for Hiding Rows
This code will hide row thirty if cell E30="". What should i do so as the
line will be hidden if E30="" and F30=""
Me.Rows(30).Hidden = CBool(Me.Range("E30").Value = "")
if range("e30")="" and range("f30")="" then rows(30).hidden =true
if .value="" and .offset(,1)="" then
Microsoft MVP Excel
"Khalil Handal" <email@example.com> wro...Counting entries from the right
For a while now I have been using some code to determine how far down a
worksheet the last line of data appears. For example:
lngLastRow = .Range("A65536").End(xlUp).Row
Does anyone know the equivalent function to count how far out from the left
(i.e. if there is data in columns A to G) - I'm sure it must exist but
cannot quite figure it out.
check out the specialcells method - XLLastCell would POSSIBLY give you the
data you want?
"Graham Whitehead" <firstname.lastname@example.org> wrote in message
news:u55DHCLiGHA.489...count query and negative numbers question
Pretty new to database queries so apologise if I sound dumb!
I have a database for stock control of energy efficient light bulbs for a
charity that gives the bulbs out free to folks in need.
The database has various fields including type of bulb, location, quantity
and whether the stock has been added to stock or removed.
I want to know how many of the different types of bulbs are in stock at any
I have tried to run a query which makes the 'quantity' amount of bulbs
removed to be negative (puts a '-' in front of number). I then want to run a
second q...Insert row VBA code
Thank you for your help. I am trying to build and find code like below
that will insert a row in columns A to P. If I put my curser on A5 and
click on a macro button then it will insert a line on A6 from column A
to P. Or if I put the curser on A10 or A50 that it would do the same
thing. I have tried the code below and I know it is wrong for the
"Entire Row" is wrong. But I cannot make it work. Thank you for your
Range("A3:P3").Insert Shift...Displaying a count as a percentage within subtotals in a pivot tab
I have a count field in a pivot table column that I want to display as a
If I have no subtotals, the option "% of column" in Value Field settings
However if I add subtotals to the column and want the percentages to
calculate WITHIN SUBTOTALS as opposed to the whole column, I can't find an
option in the Value Field settings that does that (I tried "% Of" to no
Is this even doable?
Excel 2007 Pivot Table
Subtotals with % of column
Use helper column or macro:
http://www.mediafire.com/file/iz4wwuihm1b/...adding in a row
Is there a way to add the last three none blank cells in a row.
I want to have 12 cells in a row some will have numbers and some wil
not. Just want the last three added as I add more data in the column
from left to right
~~ Message posted from http://www.ExcelTip.com
~~View and post usenet messages directly from http://www.ExcelForum.com
> Is there a way to add the last three none blank cells in a row.
I typed this pretty much verbatim into Google & found this from Peo -
How do I create a formula that returns the furthest to the right last
non zero number in a row with up to 15 columns
ie colums shown with values in cells A1 to L1
A B C D E F G H I J K L
1 4 6 0 8 4 0 7 5
How Do I create a formula to return the value of 5, in this case cell
Here's one way.
Entered with the key combo of CTRL,SHIFT,ENTER:
<email@example.com> wrote in message
> How do I create a formula that returns the furthest to the right las...Delete Empty Rows
Is there a simple macro that will enable me to delete a
row if there is no information in it. I have a very large
spread sheet but a number of the rows are empty and it
would take a considerable amount of time to delete
If there is always a blank in a certain column, you could sort on that
column to leave all of the blank rows together.
"kuri" <firstname.lastname@example.org> wrote in message
> Is there a simple macro that will enable me to delete a
> row if there is no information in it. I h...SORT Header Row(s)
My worksheet column headers are in 2 rows. Short of combining those 2 rows of
header text into 1 row, is there a way designate that there are 2 header rows
when sorting using the sort function, below?
DATE | SORT | "My data range has header row"
Maybe you can just ignore the top header row and include just the bottom header
and choose "my range has headers"...
Or maybe you could just ignore both headers and not choose that option.
I'd create a single header row. You can use alt-enter to force a new line in
>...COUNT # times text value occurrs in range
I want to count the number of times the values 'TRUE' & 'FALSE' occurr
in a list
I've used the following formula but it's returned the wrong value:
That returned the value: 34 but there are only 31 records in the lis
which are TRUE or FALSE
loscherland's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=670
View this thread: http://www.excelforum.com/showthread.php?threadid=31871
use the Countif function...can some one explain to me the count(if()) function??
i got this array formula...
where e4:e86, aq6 and aq5 are dates
i understand it...
but how does it differ to say a
can the above function be simplified
Message posted from http://www.ExcelForum.com
The COUNTIF function can handle only one condition while yours has two.
"sokevin >" <<email@example.com> wrote in message
|i got this ar...How do I count based on two conditions?
Hi there. I have a long lists of data to sort though and I am trying to write
a formula that counts that number of entries for a company with a certain
condition entered in another column. For instance
Company name Condition
Big Corp operatonal
Small corp operational
Big Corp non-operational
How do I count where Big Corp is operational and where it is non-operational?
Try this, Mark:
Hope it helps!