MIN function with a variable range
I would like to use find the minimum of a selected range in a column.
The range with be up to the current row position. The number of rows i
the range is input in another cell. I am looking for a way to construc
the range name that will work with the MIN function. I will copy th
MIN function down to other rows, but the number of rows in the MI
array will remain constant.
Example: In cell F1 is "4", indicating that I want a range 4 ro
I want to calculate the minimum of the indicated number of rows (i
this case, 5) up to the row where the MIN function is. Say mi
I have a excel file a fairly large database in one sheet and another sheet
for pivot tables from that databse. once the pivot tables are loaded the
capacity jumps from a mere 175 or 200 kb to 9 or 10 mb. and gives me
difficulties in saving the file in network drives. how can i keep the file to
low capacity and also keep the pivot tables information. I refresh the Pivot
tables only once a week and the result stays as it is till the next week.
M Imran Buhary
I used to have a similar problem. The answer, as I see it, is to
recognise that the sheet with the database in it shoudl be in ...Pivot Tables & Multiple Row Entries
When there are 1 or more descriptive fields in a pivot table and
the entries in field 2 or further on are repeated, the values in the
previous field are left blank for each repition of the subsequent
This causes problems for further sorting etc...unless you use
Does anybody know how to ensure that all cells in the descriptive
(rows in the layout section) are populated with the appropriate value?
POD's Profile: http://www.excelforum.com/member.php?action=getinfo&...Word Wrap with >1024 characters Excel 2003
I have a large number of characters in a text box and word wrap turned on, I
get the text wrapped for the first several lines and then nothing else in the
rest of the box.
Is there a way to make the remainder of the text visible, instead of rolling
off the edge of the box?
...Pivots: Grouping two fields (year, month)
My pivot table contains two fields I need grouped: "Fiscal Year" and
"Fiscal Month." Both field are numeric, not dates. The grouping would
be like this:
Fiscal Year (2003) and Fiscal Months (9-12)
Fiscal Year (2004) and Fiscal Months (1-3)
Is there a quick way to do this within the pivot? If you group the
"months", the table groups all the months regardless of
"year"....can't figure this one out.
The easiest way would be to have another column in your data table
indicating the desired grouping: you could use a formula t...Can I sort by the strike-through characters ?
Is there a way to sort the cells by the strike-through characters ?
The strike-through applies to all characters in the cell (not onl
certain characters within the cell).
Thanks in advance
~~ Message posted from http://www.ExcelTip.com
~~View and post usenet messages directly from http://www.ExcelForum.com
You've asked this 3 times already (Nov. 19, 20, 21), and I
have given you an answer twice, and MVP Tom Ogilvy gave
you a UDF to use. Do you not go back and read the
responses to your post? Here are the suggestio...Pivot Table default field setting
Working on Excel 2003
Machine One: When dragging fields to the data section of a pivot table, the
default function is "sum."
Machine Two: When dragging fields to the data section of a pivot table, the
default function is "count."
Question: Is there a way to set the default function of the field setting
prior to creating the pivot table?
Another question: Is there a way to globally change the field setting after
dragging all to the data section? I am comparing monthly data, so I have 12
fields that I drag to the data section (Jan, Feb, Mar, etc) As of now, I a...Your password cannot contain Alphabetic characters
I am using Microsoft Money 2002 and my bank has just upgraded their online
banking system. (Desert Schools Federal Credit Union) The password for the
bank use to be four-digit numeric and I used this for years in money and the
bank web site with no problems. Now my bank requires a password that contains
at least one alphabetic character to access my account. I have changed it on
the bank web site and it works fine. When I try to change the password in
Microsoft money I get “Your password cannot contain Alphabetic characters” I
am not using a character like $ or @ in the new password fo...Pivot Table border formatting and pivot chart formatting
I've haven't been able to eliminate the border formatting in a pivot
table. Every time I change the page selection it defaults back to
having borders. Does anyone know how to correct this?
I've also been having the same problem with pivot charts. When I
increase the width of a line in a chart and change the page selection
it defaults back to the previous format.
...Pivot question (2 parter)
I have four columns: Category, Year, Month, and Amount.
When I do a pivot table, I would like to have the Year and Month
appear horizontally across; right now, I can only get it to show
I would like to consolidate some of the categories and call it
"Others," rather than showing all of the details for those categories.
What is an easy way to do this?
<<Year and Month appear horizontally across>>
drag your month field to the COLUMN area in the wizard.
<<I would like to consolidate some of the categories >>
You will have to make anothe...update pivot table
I have a sheet of info. which used to create the pivot table. I need to update
this sheet every week. How can I set so every time when I update the sheet,
the pivot table will be automatically update.
Your help is very much appreciated.
If anyone would please show me how to look in the archive, that would be
If you're adding new rows of data to the source every week, you can use
a dynamic formula to name the data source range, and use that as the
source for the PivotTable. There are some instructions here:
To ...change the text in the legend of a chart w/o changing pivot text
I am trying to create a series of pie charts that compares demographic data
for clients assisted with multiple housing-related activities (new homebuyer,
rehab, foreclosure prevention, etc.) If I want to look at the age of new
homebuyers, on my pivot table I deselect all activities except new homebuyer.
After I make my pie chart, in the legend the labels are "new homebuyer
18-24", "new homebuyer 25-34", etc. It's pretty self-explanatory that these
are all "new homebuyers". I need for the New Homebuyer part to not be shown
in the legend, but I can...Pivot Table #21
In my pivot table i have days on the market. I need to sort them so that they
show in groups such as: 1-100,101-200,201-300,301-400.
then a second column showing values number sold such as:1-100 31
then a third column showing the avg. sales price suchas:1-100 31 $94,451.61
Right click on a value>Group and Outline>Group>by 100>OK
"fearthespear" <email@example.com> wrote in message
> In my pivot table i have days on the market. I need to sort them s...getting updated info from pivot tables
I created a pivot table with months as column and sum of clients an
sum of members as rows. I take the last month and Grand total number
to another table I use for reporting. Is there a formula that I ca
use to tell the table to get the last month (grand total - 1) and gran
total numbers so when I update the pivot I dont have to change the othe
table all the time.
Thanks for the help,
marksuza's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=265
View this ...Auto-send pivot table per user
I have a pivot table which is the performance benchmarking per user. Now what
I am doing is double click on user name to drill down their performance per
job, copy and plaste it on email, type the email address according to address
list, and send to them one by one.
Is there any built-in function or VBA/ macro to do these steps with one click?
Try Ron de Bruin's website, I needed to do something similar, where I
had a pivot table and I needed to email each page field of a pivot
table to a different email recipient.
The website is as follows:-
Gary Bro...grab a variable
i have a report that is currently using some code to grab a variable from a
form for display on the report..problem is that the underlying data changes
when there is more than one page to display, but it gets the same data from
the same displayed form.
hope this exaplination makes sense and somebody knows what i need to do to
i fixed by moving the code to the detail section.
...Global variable #3
Visual C++ 6.0
Is it possible to use the declaration "BYTE* buf = new BYTE" in the
..h file for global use by multiple routines?
If so, how do I clear the array to load different data into it?
> Visual C++ 6.0
> Is it possible to use the declaration "BYTE* buf = new BYTE" in the
> .h file for global use by multiple routines?
> If so, how do I clear the array to load different data into it?
How to create a global:
// h file
extern BYTE* g_buf;
// in a function in one cpp file
g_buf = new BYTE;...Pivot table grouping (URGENT!)
I have a pivot table which has currency amounts in the column and a count of
claims in the data area.
I have grouped by 100, starting at 0 running to 600.
The only thing I'm questioning is when it gets to 200 I think its including
any spend in the group 100-200 then again in the group 200-300. Is there
anyway I can get the grouping to run from 0-100, then 101-200, 201-300 etc?
Windows 98 + Office Pro 97
When you group by 100, the groups shouldn't overlap. If you start at 0,
it should automatically group by 0-99, 100-199, etc.
Adam w...pivot table trouble (tricky)
I'm making a pivot table to study information about
disease. Each disease has several characteristics like
organism that causes it, symptoms, body part it effects,
ways to detect it, ways to treat it, etc. Sometimes one
category will have multiple entries in it -- for example:
H. influenzae causes flu, there might be one treatment,
one body part if effects, but 2 ways to detect and 5
different symptoms. I want to be able to arrange and sort
my pivot table bases on any single property like the
symptom of coughing, but the only way I know to do this is
to repeat the entry many m...Macro vs Pivot table problem
I have made a copy of my workbook [Order9999] and saved it as
In the workbook I have this singel pivot-table and this singel macro
Sub Ordre9999 ()
Dim DataRange As String
DataRange = Worksheets("Inn i rapporten").Range("A16")
The macro Ordre9999 function very well.
This macro of course accordingly will be saved in new file along with
sav...Array of ints of variable size
Hi, how can I make an Array of int's with no fixed size, that is a
variable lenght arrray of ints?
Is there any MFC class to achieve so?
Yes, you can use the CArray template class. Find the right includes and the
CArray<int, int> MyIntArray;
"Martin" <firstname.lastname@example.org> wrote in message
> Hi, how can I make an Array of int's with no fixed size, that is a
> variable lenght arrray of ints?
> Is there a...Calculating a daily rate when the rate could be fixed or variable
I am working on calculating a yealy cost for a service provider. The fixed
rate in this section is $12.00 per day which will be multiplied by a certain
number of hours within a year. However, the provider is also given a blank
"exception" cell in this row in which to enter a different rate of their
chosing (could be different each time). How can I write a formula stating if
the "exception" cell is blank to look to the $12.00 daily rate and multiply
that by the hours per year. And vice versa, if there is an amount in the
exception cell, to use that daily rate and ...Remove escape characters in XML generated by XMLSerializer ?
I am using the XMLSerializer class to serialize a custom class into a memory stream whose contents i then pass to a MSSQL SP that uses the sp_xml_preparedocument SP, i can serialize the class but get secape characters in the XML when i read from the memory stream. I do not know much about encoding so i may be missing something very obvious here, heres some of the cod
_Serializer = new XmlSerializer(typeof(MyClass))
_Stream = new MemoryStream()
_Writer = new XmlTextWriter(_Stream, new UTF8Encoding())
_strXmlData = Encoding.UTF8.GetString(_St...check temp environment variable
I started Outlook this morning and a box came up saying ''could not create
work file'' check temp environment variable
Check the following key in the registry;
Verify that these variables all point to paths that exist and especially the
By default it should point to;
%USERPROFILE%\Local Settings\Temporary Internet Files
Windows Vista and Windows 7;
%USERPROFILE%\AppData\Local\Microsoft\Windows\Temporary Internet Files
These paths can often be se...Problem with series when charting from a pivot table
I have a pivot table with 1st column heading "Model" listing car models
beneath. The next column Headings are in years; 1988, 1999,..2005. In each
year column is a count of car models for the model listed in the first column.
I want to chart the model count (ver axis) vs year (hor axis), resulting in
a line for each model (6 models=six lines) showing the particular model count
for each year.
I did it yesterday and I am unable to reproduce it today. Help!
I figgered it out...
1. copy/paste table
2. remove the "model" column header