Pivot Table Cycling Through Page Fields Automatically
Hi. I am trying to cycle through a complete set of data in one of the
parameters in the "Page" field. For example, there are 500 investments, and I
want to compute the internal rate of return (IRR) for each investment based
on a series of cashflows for each investment.
The IRR is a function that is placed outside the pivot table. As each
investment number is chosen, the underlying pivot table cashflow data
changes, allow the IRR function to pick up these cashflows and compute the
IRR. However, if there are 500 investments, this becomes very time consuming
- especially if the...want to do piovt table, where rows over 100,000
i have around 100,000 rows of data...
of cousres the worksheet i can only put 66000 or so rows per sheet.
i want to do a pivot table that combines the two sheets.
how can i do this?
Message posted from http://www.ExcelForum.com
if you have that many data I would suggest you store this data in a
real database (e.g. MS Access). With Excel's pivot table wizard you can
then access this database as datasource.
Another way would be to split the data in two worksheets and within the
pivot table wizard choose the 'non contigenous ranges' for the data
Reg...Add a specific Record to a Table based on a check box
I have a Table called ServiceTypes. Based on a User's input on a
ProposalForm, ServiceTypes need to be added to a ProposalServicesTable.
For instance, I have a Check Box on the ProposalForm. When a Check Box is
clicked Yes, Access must search the ServiceTypes Table, select a specific
ServiceTypeID, and add the ServiceType to the ProposalServicesTable.
How can I add the proper Service record from the ServiceTable to the
ProposalServicesTable based on the Check Box?
I wouldn't do it that way. I'd use a listbox (with multi-select set to YES)
that was sourced to the ServiceTab...pivot table #21
I have a question on Excel. When I update a pivot table,
I used to be able to hold down the shift and ctrl keys
and highlight the area, but lately I found that I cannot
use this short cut method. Is there another short cut
method? Thanks for your help.
In step two of the Pivot Wizard, you should be able to select a starting
cell on the worksheet, then hold the Shift key, and tap the End key,
then the Down or Right arrow key, to select a range of cells.
Or, base the pivot table on a dynamic range, which will expand
automatically as new records are added. There are instructions here:...Can I delete my switchboard and make a new one?
I do not like my switchboard and would like to delete this one and start all over. But when I did delete it I couldn't make another one, because I couldn't delete the default. What should I do?Thanks,Dustin Just delete the form and the table that were created by the wizard, then start over.-- Rick B"Dustin" <Dustin@discussions.microsoft.com> wrote in message news:42F94A44-9352-4D02-9104-65593869B271@microsoft.com...>I do not like my switchboard and would like to delete this one and start >all> over. But when I did delete it I couldn't make another one, beca...transfer inbox in table format to word
In my older version I could cut and paste the table
format in Outlook to a word file. Now I don't seem to be
able to do it although I can print the file in that
format within Outlook. When I export the inbox to word I
get the whole text not just the headings. Is it possible
to transfer the table format to a word file?
...sum of a column according to two or more variables
I have a master log with a column called hours lost, a column calle
vendor, one called problem type and the rows are labeled and sorted b
I would like to sum the hours lost column for each month according t
the month and vendor, and have the sum end up in one cell
I would also like to sum the hours lost column for each month accordin
to the month and problem type and have the sum end up in one cell
basically I only want the hours lost data for a specific vendor an
month at one time or a specific problem type and month at one time, bu
I don't know how to set up the formula correctl...Convert Column to row with variable data
I'm using Excel 2k and I have a spreadsheet that looks
Fred 21 Blah St
Sue Tower 50
and need it to look like this
Name Address 1 Address2 Address3
Fred 21 Blah St London
Sue Tower 50 London EC2
I have a macro that can convert from column to row but
only for a set number of columns. Is there any way to
account for the variable amount of data for each address ?
Any help much appreciated
I suggest the following formulae - copied down as necessary:
C2: =IF(...Workflow rule on (Order)Products and columns of related entities in advanced find view
Does anyone know whether it's possible to create workflow rules on
(Order)Products, since the entity Products isn't part of the standard
In my example I have added a new (expiry) date attribute on the
Now I would like to add a workflow rule on that datefield to create a
task when the expiry date is nearly reached; but the problem I have is
that i can't "reach" the fields on the OrderProduct form to put a
workflow rule on?
Another problem I have is that I've created an advanced find query in
which I query customers who have or...outlook 2003 add a dictionary
how can add a dictionary for spell checking ?
in outlook 2003 su tools / options / spelling
actually i have only English e French.
...How do I add a hyperlink to an individual word in an Excel cell?
I am using Excel 2000 (not by choice) and I need to add a hyperlink to an
individual word within the cell, not the whole cell itself. For example in
the sentence "Click here or here to go to the appropriate web page." I want
the words "here" to each have a separate hyperlink.
Any ideas? I can manage some VBA too if necessary.
You can't do that in Excel.
You would have to use HTML or Word or some other means.
You could fake it, the entire cell would be a link, but you could
after assigning the hyperlink select another...Access, average several fields in one row
I have several rows of data in a field, I need to average all the entries in
I have 12 fields for 12 months of data, I need the average of the sum of all
non blank entries.
For example 3 months completed, the solution in Excel is
I am looking for method to average the sum in Access
One way if you can't change your data is to use a VBA function. I've posted
one below. You would call it in a calculated field in a query. Assuming your
field names are the abbreviated month names the expression might look like the
Field: fRow...pivot table -repeating "months or days" after grouping
I have data that includes the month, day, hour, min. When I run th
pivot table function to reduce the data to hourly I need the date t
repeat in the date column.
9/19/2002 0:00 2.7
9/19/2002 0:10 3.7
9/19/2002 0:20 3.8
9/19/2002 0:30 4.6
9/19/2002 0:40 4.8
9/19/2002 0:50 4.8
9/19/2002 1:00 5.6
9/19/2002 1:10 5.2
What I get when I group the Pivot table:
Sep 19-Sep 12 AM 2.7
- - 1 AM 3.7
- - 2 AM 3.8
- - 3 AM 4.6
- - 4 AM 4.8
What I want is:
Sep 19-Sep 12 AM 2.7
- 19-Sep 1 AM 3.7...Create interactive pivot table chart based on item selected
I'm trying to remember how to drag a chart object to the top left cell of a
pivot table thus displaying a charted image of the detail item selected. Any
...want to add all $ in column c where column A is the same
I'm very new at Excel, and a real math dummy. I've
figured out how to enter a formula when all the $ I want
to add are together, but I can't figure out how to do
that when I want to select only the $ values for certain
items listed throughout the spreadsheet. For example:
I keep a running list of Architects, their projects and $
values of each project as they are assigned. I want to
automatically calculate the total current $ value for
each Architect without having to sort them in order, or
create a separate table for each architect. Can I do
Here's what th...reflecting values in a column into a row
I am creating a chart to map a round-robin chess game. If there are 4
players, then all 4 has to play one another.
if I have the names
Then I'd like to type them into a columns and write a formula in a
row to pick up the names
the spreadsheet should then look like this:
John Mike Sally Bill
I think it may be achieved with the Indirect() function, but my Excel
2007 help seems broken and I can't figure it out without an example.
With names in A2:A5
Enter in B1 =INDIRECT("A"&COLUMN(B1))
Or...Remove last letter from column
Hi, I have a list of titles and some titles have a letter A or B at
the end.. is there a function/formula I can use to remove them if it
ends in A or B?
For example (my list):
Accounting Sr Mgr B
Accounts Payable Sr Mgr B
Ambulatory Plng Sr Prog Dir A
Need it to look like this:
Accounting Sr Mgr
Accounts Payable Sr Mgr
Ambulatory Plng Sr Prog Dir
This will get rid of the A or B at the end along with the space before
it. Assuming the value is in A5:
=IF(OR(RIGHT(A5,2)=" B",RIGHT(A5,2)=" A"),LEFT(A5,LEN(A5)-2),A5)
> Hi, I have a list of titles a...problem in changing the text of sentences before tables
I am developing a word automation application. In a method of mine, I change
the text of some sentences of an opened word file, but the problem is when I
change the text of a sentence which located before a table, it will be moved
to the first cell of the table. My code is as follow:
void myMethod( long startingSentenceNumber, const char *toBeSearched, const
char *replacement, bool replace )
Sentences sentencesList = m_document.GetSentences();
long sentencesCount = sentencesList.GetCount();
CString replacementCStr(...Is store procedure always fast than Access linked table via ODBC?
I was assigned to upgrade one program from Access(using ODBC to connect to
SQL 2000) to ASP.NET(using store procedure in SQL 2000).
Finally, I tested them and found that ASP.NET is slower than Access.
The mojority job of program is select some data from SQL 2000 tables, modify
and then insert into some tables.
Is store procedure always fast than Access linked table via ODBC?
Message posted via http://www.sqlmonster.com
Stored procedures don’t add any overhead and they can save compile time.
It's the code in the stored procedure and the underlying tables / indexes
that ...graph add ins for dials or dashboard reports?
I am trying to create some dashboard reporting at my company and cannot find
any addins for this purpose. Can anyone help ???
I've heard from a few people that like Charley Kidd's dashboards. I
haven't tried them:
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
> I am trying to create some dashboard reporting at my company and cannot find
> any addins for this purpose. Can anyone help ???
...Change Row/Column Height & Width
I know I should be able to automatically set a row height to the max
necessary by hovering the cursor between the 2 rows I want to adjust and
double-clicking, but sometimes this doesn't always work. Why is that that -
do I need to adjust a setting? And is there any way to set it so that if
text is added or deleted the row height would change automatically so thatthe
text fit appropriately?
Set the row format to Autofit and cells to Wrap Text
Gord Dibben MS Excel MVP
On Tue, 19 May 2009 12:14:04 -0700, DaveL <DaveL@discussions.microsoft.com>
>I know I should be ...need to make a formula that would add a field value to current dat
I have made a form in which I input different values. On of the values is
(How Many Days). Now I need to a assign a default value, or expression (not
sure which way to go about this) that will take the date value for (Date) and
add the value (How Many Days)
I figured that the formula should read =sum([Date]+[How Many Days])
But that is not giving me any results, thanx for your help in advance
=DateDiff("d", Date(), [How Many Days])
"J Man" wrote:
> I have made a form in which I input different values. On of the values is ...When I open a workbook, another one called Book1 is also opened. W
This Book1 becomes the current book. If I go to Window on the toolbar I can
switch to my correct workbook. Why is this Book1 being opened? I can cure it
by closing Book1, then saving my correct workbook, then closing that one. The
next time I open my workbook, Book1 has disappeared.
Do you mean two different sheets within the same workbook?
> This Book1 becomes the current book. If I go to Window on the toolbar I can
> switch to my correct workbook. Why is this Book1 being opened? I can cure it
> by closing Book1, then saving my correct workbo...How do I show a data table with legend keys in a line chart?
I am having trouble inserting a data table with legend keys into a line chart
that contains a horizontal target line. Can anyone help me?
I tried it in both 2007 and 2003 and had no problems. We need more
infomation, can you post a sample of the chart somewhere. The only issue I
have is that the target line is displayed on the data table, which I would
prefer that it not be.
> I am having trouble inserting a data table with legend keys into a line chart
> that contains a horizontal target line. Can anyone he...One Exchange server wont send mail to certain domains
Just wondering if I can pick someones brains.
We have 2 Exchange servers, 1 2000 (Ex2000 Sp3, Win2k SP4, Global Catalogue,
member), and 1 2003 server (Ex2003 Sp2, Win2k3 SP2, master).
Since putting in the 2003 server, users on the 2000 server get some "mail
server rejected connection" failures when sending to 1 or 2 domains, most
other domains accept mail ok. Users on the 2003 box can send fine. We've
checked our MX records which are in place on the firewall, and we can also
telnet to these domains and complete the telnet test.
2003 > outside world (u...