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 underlying cashflow change constantly. 

How could I cycle through the investments and copy the results from the IRR 
to a separate table? (I can create the copy routine, but have not been 
successful with the cycling through the pivot table page field). 

Many thanks!

0
nrehman1 (9)
9/2/2005 3:51:59 PM
excel.misc 78881 articles. 5 followers. Follow

14 Replies
478 Views

Similar Articles

[PageSpeed] 1

There's sample code here for printing each item in the page field:

   http://www.contextures.com/xlPivot09.html

You could adapt that to your workbook.

nrehman wrote:
> 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 underlying cashflow change constantly. 
> 
> How could I cycle through the investments and copy the results from the IRR 
> to a separate table? (I can create the copy routine, but have not been 
> successful with the cycling through the pivot table page field). 
> 
> Many thanks!
> 


-- 
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

0
dsd1 (5911)
9/2/2005 6:04:24 PM
Debra Dalgleish has some sample code at:
http://contextures.com/xlPivot09.html

I bet you could modify it to do what you want.

nrehman wrote:
> 
> 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 underlying cashflow change constantly.
> 
> How could I cycle through the investments and copy the results from the IRR
> to a separate table? (I can create the copy routine, but have not been
> successful with the cycling through the pivot table page field).
> 
> Many thanks!

-- 

Dave Peterson
0
petersod (12004)
9/2/2005 6:06:02 PM
Thank you!  I should be able to adapt this code.

"Debra Dalgleish" wrote:

> There's sample code here for printing each item in the page field:
> 
>    http://www.contextures.com/xlPivot09.html
> 
> You could adapt that to your workbook.
> 
> nrehman wrote:
> > 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 underlying cashflow change constantly. 
> > 
> > How could I cycle through the investments and copy the results from the IRR 
> > to a separate table? (I can create the copy routine, but have not been 
> > successful with the cycling through the pivot table page field). 
> > 
> > Many thanks!
> > 
> 
> 
> -- 
> Debra Dalgleish
> Excel FAQ, Tips & Book List
> http://www.contextures.com/tiptech.html
> 
> 
0
nrehman1 (9)
9/2/2005 6:19:01 PM
This worked great!  Thank you.

However, as my sample has now expanded beyond a couple of hundred pivot 
items.  There are now over two thousand unique items and I am faced with the 
error that effectively says there are too many items for the pivotitems 
field.  (I believe this is a function of the number of unique items by the 
columns adding up to some number which cannot be exceeded).

Is there a work around?  I would like to be able to generate the results for 
all the items at one shot as it speeds the work and precludes human error.

Many thanks.



"Debra Dalgleish" wrote:

> There's sample code here for printing each item in the page field:
> 
>    http://www.contextures.com/xlPivot09.html
> 
> You could adapt that to your workbook.
> 
> nrehman wrote:
> > 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 underlying cashflow change constantly. 
> > 
> > How could I cycle through the investments and copy the results from the IRR 
> > to a separate table? (I can create the copy routine, but have not been 
> > successful with the cycling through the pivot table page field). 
> > 
> > Many thanks!
> > 
> 
> 
> -- 
> Debra Dalgleish
> Excel FAQ, Tips & Book List
> http://www.contextures.com/tiptech.html
> 
> 
0
nrehman1 (9)
9/29/2005 7:34:04 PM
The page field should allow more than 2000 items. What version of Excel 
are you using?


nrehman wrote:
> This worked great!  Thank you.
> 
> However, as my sample has now expanded beyond a couple of hundred pivot 
> items.  There are now over two thousand unique items and I am faced with the 
> error that effectively says there are too many items for the pivotitems 
> field.  (I believe this is a function of the number of unique items by the 
> columns adding up to some number which cannot be exceeded).
> 
> Is there a work around?  I would like to be able to generate the results for 
> all the items at one shot as it speeds the work and precludes human error.
> 
> Many thanks.
> 
> 
> 
> "Debra Dalgleish" wrote:
> 
> 
>>There's sample code here for printing each item in the page field:
>>
>>   http://www.contextures.com/xlPivot09.html
>>
>>You could adapt that to your workbook.
>>
>>nrehman wrote:
>>
>>>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 underlying cashflow change constantly. 
>>>
>>>How could I cycle through the investments and copy the results from the IRR 
>>>to a separate table? (I can create the copy routine, but have not been 
>>>successful with the cycling through the pivot table page field). 
>>>
>>>Many thanks!
>>>
>>
>>
>>-- 
>>Debra Dalgleish
>>Excel FAQ, Tips & Book List
>>http://www.contextures.com/tiptech.html
>>
>>
> 


-- 
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

0
dsd1 (5911)
10/3/2005 7:23:01 PM
Thank you for your reply.

You are absolutely correct.  Creating the identical parameters to cause the 
same "bug", I found that I had created another variable which was crashing 
the code.  Once I removed that, the program ran fine (which is really great)!

I will be coming down to another wall soon, which the the maximum number of 
rows (I have almost 65000 rows by 6 columns of data).  Would you have any 
ideas for how to work around this issue?  Is there some way in the vb to 
code/join two worksheets or range two sets of columns for a pivot table?  (If 
you prefer, I can submit a new item for the forum).

Thanks again for your invaluable help.

Nadir


"Debra Dalgleish" wrote:

> The page field should allow more than 2000 items. What version of Excel 
> are you using?
> 
> 
> nrehman wrote:
> > This worked great!  Thank you.
> > 
> > However, as my sample has now expanded beyond a couple of hundred pivot 
> > items.  There are now over two thousand unique items and I am faced with the 
> > error that effectively says there are too many items for the pivotitems 
> > field.  (I believe this is a function of the number of unique items by the 
> > columns adding up to some number which cannot be exceeded).
> > 
> > Is there a work around?  I would like to be able to generate the results for 
> > all the items at one shot as it speeds the work and precludes human error.
> > 
> > Many thanks.
> > 
> > 
> > 
> > "Debra Dalgleish" wrote:
> > 
> > 
> >>There's sample code here for printing each item in the page field:
> >>
> >>   http://www.contextures.com/xlPivot09.html
> >>
> >>You could adapt that to your workbook.
> >>
> >>nrehman wrote:
> >>
> >>>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 underlying cashflow change constantly. 
> >>>
> >>>How could I cycle through the investments and copy the results from the IRR 
> >>>to a separate table? (I can create the copy routine, but have not been 
> >>>successful with the cycling through the pivot table page field). 
> >>>
> >>>Many thanks!
> >>>
> >>
> >>
> >>-- 
> >>Debra Dalgleish
> >>Excel FAQ, Tips & Book List
> >>http://www.contextures.com/tiptech.html
> >>
> >>
> > 
> 
> 
> -- 
> Debra Dalgleish
> Excel FAQ, Tips & Book List
> http://www.contextures.com/tiptech.html
> 
> 
0
nrehman1 (9)
10/3/2005 7:56:06 PM
You could use multiple consolidation ranges, but the results are too 
good. There's an example, and a couple of workarounds, here:

   http://www.contextures.com/xlPivot08.html


nrehman wrote:
> Thank you for your reply.
> 
> You are absolutely correct.  Creating the identical parameters to cause the 
> same "bug", I found that I had created another variable which was crashing 
> the code.  Once I removed that, the program ran fine (which is really great)!
> 
> I will be coming down to another wall soon, which the the maximum number of 
> rows (I have almost 65000 rows by 6 columns of data).  Would you have any 
> ideas for how to work around this issue?  Is there some way in the vb to 
> code/join two worksheets or range two sets of columns for a pivot table?  (If 
> you prefer, I can submit a new item for the forum).
> 
> Thanks again for your invaluable help.
> 
> Nadir
> 
> 
> "Debra Dalgleish" wrote:
> 
> 
>>The page field should allow more than 2000 items. What version of Excel 
>>are you using?
>>
>>
>>nrehman wrote:
>>
>>>This worked great!  Thank you.
>>>
>>>However, as my sample has now expanded beyond a couple of hundred pivot 
>>>items.  There are now over two thousand unique items and I am faced with the 
>>>error that effectively says there are too many items for the pivotitems 
>>>field.  (I believe this is a function of the number of unique items by the 
>>>columns adding up to some number which cannot be exceeded).
>>>
>>>Is there a work around?  I would like to be able to generate the results for 
>>>all the items at one shot as it speeds the work and precludes human error.
>>>
>>>Many thanks.
>>>
>>>
>>>
>>>"Debra Dalgleish" wrote:
>>>
>>>
>>>
>>>>There's sample code here for printing each item in the page field:
>>>>
>>>>  http://www.contextures.com/xlPivot09.html
>>>>
>>>>You could adapt that to your workbook.
>>>>
>>>>nrehman wrote:
>>>>
>>>>
>>>>>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 underlying cashflow change constantly. 
>>>>>
>>>>>How could I cycle through the investments and copy the results from the IRR 
>>>>>to a separate table? (I can create the copy routine, but have not been 
>>>>>successful with the cycling through the pivot table page field). 
>>>>>
>>>>>Many thanks!
>>>>>
>>>>
>>>>
>>>>-- 
>>>>Debra Dalgleish
>>>>Excel FAQ, Tips & Book List
>>>>http://www.contextures.com/tiptech.html
>>>>
>>>>
>>>
>>
>>-- 
>>Debra Dalgleish
>>Excel FAQ, Tips & Book List
>>http://www.contextures.com/tiptech.html
>>
>>
> 


-- 
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

0
dsd1 (5911)
10/3/2005 9:07:50 PM
Thanks again.

I created a new database query (the "Get Data" was trying to link to data on 
a directory on your PC when I followed the instructions).  Then created a new 
SQL based on you SQL.  This worked, creating a new pivot table which matched 
your results.

I tried to do the same with some of my data, creating named regions for two 
identical worksheets (say using Ontario and Alberta as the worksheet names).  
However, I have the curious result of seeing ONLY ONE set of data (from one 
named region) when the first column is identical in both of the worksheets 
with data.  When I change the first column in the Ontario to be different 
from the first column in Alberta, I then see the two sets of data.

Do the first columns have to have different data (?).  And therefore have to 
have data that is unique to one worksheet as compared to the other?  Is there 
a step that I have missed in consolidating the data which causes identical 
data to not be unioned?

Thanks!


"Debra Dalgleish" wrote:

> You could use multiple consolidation ranges, but the results are too 
> good. There's an example, and a couple of workarounds, here:
> 
>    http://www.contextures.com/xlPivot08.html
> 
> 
> nrehman wrote:
> > Thank you for your reply.
> > 
> > You are absolutely correct.  Creating the identical parameters to cause the 
> > same "bug", I found that I had created another variable which was crashing 
> > the code.  Once I removed that, the program ran fine (which is really great)!
> > 
> > I will be coming down to another wall soon, which the the maximum number of 
> > rows (I have almost 65000 rows by 6 columns of data).  Would you have any 
> > ideas for how to work around this issue?  Is there some way in the vb to 
> > code/join two worksheets or range two sets of columns for a pivot table?  (If 
> > you prefer, I can submit a new item for the forum).
> > 
> > Thanks again for your invaluable help.
> > 
> > Nadir
> > 
> > 
> > "Debra Dalgleish" wrote:
> > 
> > 
> >>The page field should allow more than 2000 items. What version of Excel 
> >>are you using?
> >>
> >>
> >>nrehman wrote:
> >>
> >>>This worked great!  Thank you.
> >>>
> >>>However, as my sample has now expanded beyond a couple of hundred pivot 
> >>>items.  There are now over two thousand unique items and I am faced with the 
> >>>error that effectively says there are too many items for the pivotitems 
> >>>field.  (I believe this is a function of the number of unique items by the 
> >>>columns adding up to some number which cannot be exceeded).
> >>>
> >>>Is there a work around?  I would like to be able to generate the results for 
> >>>all the items at one shot as it speeds the work and precludes human error.
> >>>
> >>>Many thanks.
> >>>
> >>>
> >>>
> >>>"Debra Dalgleish" wrote:
> >>>
> >>>
> >>>
> >>>>There's sample code here for printing each item in the page field:
> >>>>
> >>>>  http://www.contextures.com/xlPivot09.html
> >>>>
> >>>>You could adapt that to your workbook.
> >>>>
> >>>>nrehman wrote:
> >>>>
> >>>>
> >>>>>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 underlying cashflow change constantly. 
> >>>>>
> >>>>>How could I cycle through the investments and copy the results from the IRR 
> >>>>>to a separate table? (I can create the copy routine, but have not been 
> >>>>>successful with the cycling through the pivot table page field). 
> >>>>>
> >>>>>Many thanks!
> >>>>>
> >>>>
> >>>>
> >>>>-- 
> >>>>Debra Dalgleish
> >>>>Excel FAQ, Tips & Book List
> >>>>http://www.contextures.com/tiptech.html
> >>>>
> >>>>
> >>>
> >>
> >>-- 
> >>Debra Dalgleish
> >>Excel FAQ, Tips & Book List
> >>http://www.contextures.com/tiptech.html
> >>
> >>
> > 
> 
> 
> -- 
> Debra Dalgleish
> Excel FAQ, Tips & Book List
> http://www.contextures.com/tiptech.html
> 
> 
0
nrehman1 (9)
10/3/2005 10:43:05 PM
It should work with identical data, if you use UNION ALL in the SQL 
statement.

nrehman wrote:
> Thanks again.
> 
> I created a new database query (the "Get Data" was trying to link to data on 
> a directory on your PC when I followed the instructions).  Then created a new 
> SQL based on you SQL.  This worked, creating a new pivot table which matched 
> your results.
> 
> I tried to do the same with some of my data, creating named regions for two 
> identical worksheets (say using Ontario and Alberta as the worksheet names).  
> However, I have the curious result of seeing ONLY ONE set of data (from one 
> named region) when the first column is identical in both of the worksheets 
> with data.  When I change the first column in the Ontario to be different 
> from the first column in Alberta, I then see the two sets of data.
> 
> Do the first columns have to have different data (?).  And therefore have to 
> have data that is unique to one worksheet as compared to the other?  Is there 
> a step that I have missed in consolidating the data which causes identical 
> data to not be unioned?
> 
> Thanks!
> 
> 
> "Debra Dalgleish" wrote:
> 
> 
>>You could use multiple consolidation ranges, but the results are too 
>>good. There's an example, and a couple of workarounds, here:
>>
>>   http://www.contextures.com/xlPivot08.html
>>
>>
>>nrehman wrote:
>>
>>>Thank you for your reply.
>>>
>>>You are absolutely correct.  Creating the identical parameters to cause the 
>>>same "bug", I found that I had created another variable which was crashing 
>>>the code.  Once I removed that, the program ran fine (which is really great)!
>>>
>>>I will be coming down to another wall soon, which the the maximum number of 
>>>rows (I have almost 65000 rows by 6 columns of data).  Would you have any 
>>>ideas for how to work around this issue?  Is there some way in the vb to 
>>>code/join two worksheets or range two sets of columns for a pivot table?  (If 
>>>you prefer, I can submit a new item for the forum).
>>>
>>>Thanks again for your invaluable help.
>>>
>>>Nadir
>>>
>>>
>>>"Debra Dalgleish" wrote:
>>>
>>>
>>>
>>>>The page field should allow more than 2000 items. What version of Excel 
>>>>are you using?
>>>>
>>>>
>>>>nrehman wrote:
>>>>
>>>>
>>>>>This worked great!  Thank you.
>>>>>
>>>>>However, as my sample has now expanded beyond a couple of hundred pivot 
>>>>>items.  There are now over two thousand unique items and I am faced with the 
>>>>>error that effectively says there are too many items for the pivotitems 
>>>>>field.  (I believe this is a function of the number of unique items by the 
>>>>>columns adding up to some number which cannot be exceeded).
>>>>>
>>>>>Is there a work around?  I would like to be able to generate the results for 
>>>>>all the items at one shot as it speeds the work and precludes human error.
>>>>>
>>>>>Many thanks.
>>>>>
>>>>>
>>>>>
>>>>>"Debra Dalgleish" wrote:
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>>There's sample code here for printing each item in the page field:
>>>>>>
>>>>>> http://www.contextures.com/xlPivot09.html
>>>>>>
>>>>>>You could adapt that to your workbook.
>>>>>>
>>>>>>nrehman wrote:
>>>>>>
>>>>>>
>>>>>>
>>>>>>>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 underlying cashflow change constantly. 
>>>>>>>
>>>>>>>How could I cycle through the investments and copy the results from the IRR 
>>>>>>>to a separate table? (I can create the copy routine, but have not been 
>>>>>>>successful with the cycling through the pivot table page field). 
>>>>>>>
>>>>>>>Many thanks!
>>>>>>>
>>>>>>
>>>>>>
>>>>>>-- 
>>>>>>Debra Dalgleish
>>>>>>Excel FAQ, Tips & Book List
>>>>>>http://www.contextures.com/tiptech.html
>>>>>>
>>>>>>
>>>>>
>>>>-- 
>>>>Debra Dalgleish
>>>>Excel FAQ, Tips & Book List
>>>>http://www.contextures.com/tiptech.html
>>>>
>>>>
>>>
>>
>>-- 
>>Debra Dalgleish
>>Excel FAQ, Tips & Book List
>>http://www.contextures.com/tiptech.html
>>
>>
> 


-- 
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

0
dsd1 (5911)
10/4/2005 12:48:05 AM
Pure genius.  It's a pleasure to know someone who has mastery of their art.

A capital thank you!



"Debra Dalgleish" wrote:

> It should work with identical data, if you use UNION ALL in the SQL 
> statement.
> 
> nrehman wrote:
> > Thanks again.
> > 
> > I created a new database query (the "Get Data" was trying to link to data on 
> > a directory on your PC when I followed the instructions).  Then created a new 
> > SQL based on you SQL.  This worked, creating a new pivot table which matched 
> > your results.
> > 
> > I tried to do the same with some of my data, creating named regions for two 
> > identical worksheets (say using Ontario and Alberta as the worksheet names).  
> > However, I have the curious result of seeing ONLY ONE set of data (from one 
> > named region) when the first column is identical in both of the worksheets 
> > with data.  When I change the first column in the Ontario to be different 
> > from the first column in Alberta, I then see the two sets of data.
> > 
> > Do the first columns have to have different data (?).  And therefore have to 
> > have data that is unique to one worksheet as compared to the other?  Is there 
> > a step that I have missed in consolidating the data which causes identical 
> > data to not be unioned?
> > 
> > Thanks!
> > 
> > 
> > "Debra Dalgleish" wrote:
> > 
> > 
> >>You could use multiple consolidation ranges, but the results are too 
> >>good. There's an example, and a couple of workarounds, here:
> >>
> >>   http://www.contextures.com/xlPivot08.html
> >>
> >>
> >>nrehman wrote:
> >>
> >>>Thank you for your reply.
> >>>
> >>>You are absolutely correct.  Creating the identical parameters to cause the 
> >>>same "bug", I found that I had created another variable which was crashing 
> >>>the code.  Once I removed that, the program ran fine (which is really great)!
> >>>
> >>>I will be coming down to another wall soon, which the the maximum number of 
> >>>rows (I have almost 65000 rows by 6 columns of data).  Would you have any 
> >>>ideas for how to work around this issue?  Is there some way in the vb to 
> >>>code/join two worksheets or range two sets of columns for a pivot table?  (If 
> >>>you prefer, I can submit a new item for the forum).
> >>>
> >>>Thanks again for your invaluable help.
> >>>
> >>>Nadir
> >>>
> >>>
> >>>"Debra Dalgleish" wrote:
> >>>
> >>>
> >>>
> >>>>The page field should allow more than 2000 items. What version of Excel 
> >>>>are you using?
> >>>>
> >>>>
> >>>>nrehman wrote:
> >>>>
> >>>>
> >>>>>This worked great!  Thank you.
> >>>>>
> >>>>>However, as my sample has now expanded beyond a couple of hundred pivot 
> >>>>>items.  There are now over two thousand unique items and I am faced with the 
> >>>>>error that effectively says there are too many items for the pivotitems 
> >>>>>field.  (I believe this is a function of the number of unique items by the 
> >>>>>columns adding up to some number which cannot be exceeded).
> >>>>>
> >>>>>Is there a work around?  I would like to be able to generate the results for 
> >>>>>all the items at one shot as it speeds the work and precludes human error.
> >>>>>
> >>>>>Many thanks.
> >>>>>
> >>>>>
> >>>>>
> >>>>>"Debra Dalgleish" wrote:
> >>>>>
> >>>>>
> >>>>>
> >>>>>
> >>>>>>There's sample code here for printing each item in the page field:
> >>>>>>
> >>>>>> http://www.contextures.com/xlPivot09.html
> >>>>>>
> >>>>>>You could adapt that to your workbook.
> >>>>>>
> >>>>>>nrehman wrote:
> >>>>>>
> >>>>>>
> >>>>>>
> >>>>>>>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 underlying cashflow change constantly. 
> >>>>>>>
> >>>>>>>How could I cycle through the investments and copy the results from the IRR 
> >>>>>>>to a separate table? (I can create the copy routine, but have not been 
> >>>>>>>successful with the cycling through the pivot table page field). 
> >>>>>>>
> >>>>>>>Many thanks!
> >>>>>>>
> >>>>>>
> >>>>>>
> >>>>>>-- 
> >>>>>>Debra Dalgleish
> >>>>>>Excel FAQ, Tips & Book List
> >>>>>>http://www.contextures.com/tiptech.html
> >>>>>>
> >>>>>>
> >>>>>
> >>>>-- 
> >>>>Debra Dalgleish
> >>>>Excel FAQ, Tips & Book List
> >>>>http://www.contextures.com/tiptech.html
> >>>>
> >>>>
> >>>
> >>
> >>-- 
> >>Debra Dalgleish
> >>Excel FAQ, Tips & Book List
> >>http://www.contextures.com/tiptech.html
> >>
> >>
> > 
> 
> 
> -- 
> Debra Dalgleish
> Excel FAQ, Tips & Book List
> http://www.contextures.com/tiptech.html
> 
> 
0
nrehman1 (9)
10/4/2005 1:36:01 PM
You're welcome! Thanks for letting me know that it worked.

nrehman wrote:
> Pure genius.  It's a pleasure to know someone who has mastery of their art.
> 
> A capital thank you!
> 
> 
> 
> "Debra Dalgleish" wrote:
> 
> 
>>It should work with identical data, if you use UNION ALL in the SQL 
>>statement.
>>
>>nrehman wrote:
>>
>>>Thanks again.
>>>
>>>I created a new database query (the "Get Data" was trying to link to data on 
>>>a directory on your PC when I followed the instructions).  Then created a new 
>>>SQL based on you SQL.  This worked, creating a new pivot table which matched 
>>>your results.
>>>
>>>I tried to do the same with some of my data, creating named regions for two 
>>>identical worksheets (say using Ontario and Alberta as the worksheet names).  
>>>However, I have the curious result of seeing ONLY ONE set of data (from one 
>>>named region) when the first column is identical in both of the worksheets 
>>>with data.  When I change the first column in the Ontario to be different 
>>>from the first column in Alberta, I then see the two sets of data.
>>>
>>>Do the first columns have to have different data (?).  And therefore have to 
>>>have data that is unique to one worksheet as compared to the other?  Is there 
>>>a step that I have missed in consolidating the data which causes identical 
>>>data to not be unioned?
>>>
>>>Thanks!
>>>
>>>
>>>"Debra Dalgleish" wrote:
>>>
>>>
>>>
>>>>You could use multiple consolidation ranges, but the results are too 
>>>>good. There's an example, and a couple of workarounds, here:
>>>>
>>>>  http://www.contextures.com/xlPivot08.html
>>>>
>>>>
>>>>nrehman wrote:
>>>>
>>>>
>>>>>Thank you for your reply.
>>>>>
>>>>>You are absolutely correct.  Creating the identical parameters to cause the 
>>>>>same "bug", I found that I had created another variable which was crashing 
>>>>>the code.  Once I removed that, the program ran fine (which is really great)!
>>>>>
>>>>>I will be coming down to another wall soon, which the the maximum number of 
>>>>>rows (I have almost 65000 rows by 6 columns of data).  Would you have any 
>>>>>ideas for how to work around this issue?  Is there some way in the vb to 
>>>>>code/join two worksheets or range two sets of columns for a pivot table?  (If 
>>>>>you prefer, I can submit a new item for the forum).
>>>>>
>>>>>Thanks again for your invaluable help.
>>>>>
>>>>>Nadir
>>>>>
>>>>>
>>>>>"Debra Dalgleish" wrote:
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>>The page field should allow more than 2000 items. What version of Excel 
>>>>>>are you using?
>>>>>>
>>>>>>
>>>>>>nrehman wrote:
>>>>>>
>>>>>>
>>>>>>
>>>>>>>This worked great!  Thank you.
>>>>>>>
>>>>>>>However, as my sample has now expanded beyond a couple of hundred pivot 
>>>>>>>items.  There are now over two thousand unique items and I am faced with the 
>>>>>>>error that effectively says there are too many items for the pivotitems 
>>>>>>>field.  (I believe this is a function of the number of unique items by the 
>>>>>>>columns adding up to some number which cannot be exceeded).
>>>>>>>
>>>>>>>Is there a work around?  I would like to be able to generate the results for 
>>>>>>>all the items at one shot as it speeds the work and precludes human error.
>>>>>>>
>>>>>>>Many thanks.
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>"Debra Dalgleish" wrote:
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>>There's sample code here for printing each item in the page field:
>>>>>>>>
>>>>>>>>http://www.contextures.com/xlPivot09.html
>>>>>>>>
>>>>>>>>You could adapt that to your workbook.
>>>>>>>>
>>>>>>>>nrehman wrote:
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>>>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 underlying cashflow change constantly. 
>>>>>>>>>
>>>>>>>>>How could I cycle through the investments and copy the results from the IRR 
>>>>>>>>>to a separate table? (I can create the copy routine, but have not been 
>>>>>>>>>successful with the cycling through the pivot table page field). 
>>>>>>>>>
>>>>>>>>>Many thanks!
>>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>>-- 
>>>>>>>>Debra Dalgleish
>>>>>>>>Excel FAQ, Tips & Book List
>>>>>>>>http://www.contextures.com/tiptech.html
>>>>>>>>
>>>>>>>>
>>>>>>>
>>>>>>-- 
>>>>>>Debra Dalgleish
>>>>>>Excel FAQ, Tips & Book List
>>>>>>http://www.contextures.com/tiptech.html
>>>>>>
>>>>>>
>>>>>
>>>>-- 
>>>>Debra Dalgleish
>>>>Excel FAQ, Tips & Book List
>>>>http://www.contextures.com/tiptech.html
>>>>
>>>>
>>>
>>
>>-- 
>>Debra Dalgleish
>>Excel FAQ, Tips & Book List
>>http://www.contextures.com/tiptech.html
>>
>>
> 


-- 
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

0
dsd1 (5911)
10/4/2005 1:58:40 PM
Progress is sometimes painful.  After trying to decode the routine, I am 
trying to add two capabilities to the cycling routine, but have had little 
success (probably due to lack of both knowledge and correct implementation):

1)  Include the "All" results 
Here I would like to include the "All" results for each of the pagefields.  
I have tried to modify the pivotitem For Next loops to start from "0", but 
the code did not like this and crashes.

and 

2) Exclude pagefields which create parameters for the data to cycle within.  
Here I have have tried to modify the pagefield counts in multiple places, 
but have not been successful.  The idea was that if I could get the pagefield 
count to be less than the number of pagefields (and the parameter pagefields 
were placed below the cycling pagefields), then the parameter pagefields 
would be ignored.

ANY suggestions?

Thanks you.

"Debra Dalgleish" wrote:

> You're welcome! Thanks for letting me know that it worked.
> 
> nrehman wrote:
> > Pure genius.  It's a pleasure to know someone who has mastery of their art.
> > 
> > A capital thank you!
> > 
> > 
> > 
> > "Debra Dalgleish" wrote:
> > 
> > 
> >>It should work with identical data, if you use UNION ALL in the SQL 
> >>statement.
> >>
> >>nrehman wrote:
> >>
> >>>Thanks again.
> >>>
> >>>I created a new database query (the "Get Data" was trying to link to data on 
> >>>a directory on your PC when I followed the instructions).  Then created a new 
> >>>SQL based on you SQL.  This worked, creating a new pivot table which matched 
> >>>your results.
> >>>
> >>>I tried to do the same with some of my data, creating named regions for two 
> >>>identical worksheets (say using Ontario and Alberta as the worksheet names).  
> >>>However, I have the curious result of seeing ONLY ONE set of data (from one 
> >>>named region) when the first column is identical in both of the worksheets 
> >>>with data.  When I change the first column in the Ontario to be different 
> >>>from the first column in Alberta, I then see the two sets of data.
> >>>
> >>>Do the first columns have to have different data (?).  And therefore have to 
> >>>have data that is unique to one worksheet as compared to the other?  Is there 
> >>>a step that I have missed in consolidating the data which causes identical 
> >>>data to not be unioned?
> >>>
> >>>Thanks!
> >>>
> >>>
> >>>"Debra Dalgleish" wrote:
> >>>
> >>>
> >>>
> >>>>You could use multiple consolidation ranges, but the results are too 
> >>>>good. There's an example, and a couple of workarounds, here:
> >>>>
> >>>>  http://www.contextures.com/xlPivot08.html
> >>>>
> >>>>
> >>>>nrehman wrote:
> >>>>
> >>>>
> >>>>>Thank you for your reply.
> >>>>>
> >>>>>You are absolutely correct.  Creating the identical parameters to cause the 
> >>>>>same "bug", I found that I had created another variable which was crashing 
> >>>>>the code.  Once I removed that, the program ran fine (which is really great)!
> >>>>>
> >>>>>I will be coming down to another wall soon, which the the maximum number of 
> >>>>>rows (I have almost 65000 rows by 6 columns of data).  Would you have any 
> >>>>>ideas for how to work around this issue?  Is there some way in the vb to 
> >>>>>code/join two worksheets or range two sets of columns for a pivot table?  (If 
> >>>>>you prefer, I can submit a new item for the forum).
> >>>>>
> >>>>>Thanks again for your invaluable help.
> >>>>>
> >>>>>Nadir
> >>>>>
> >>>>>
> >>>>>"Debra Dalgleish" wrote:
> >>>>>
> >>>>>
> >>>>>
> >>>>>
> >>>>>>The page field should allow more than 2000 items. What version of Excel 
> >>>>>>are you using?
> >>>>>>
> >>>>>>
> >>>>>>nrehman wrote:
> >>>>>>
> >>>>>>
> >>>>>>
> >>>>>>>This worked great!  Thank you.
> >>>>>>>
> >>>>>>>However, as my sample has now expanded beyond a couple of hundred pivot 
> >>>>>>>items.  There are now over two thousand unique items and I am faced with the 
> >>>>>>>error that effectively says there are too many items for the pivotitems 
> >>>>>>>field.  (I believe this is a function of the number of unique items by the 
> >>>>>>>columns adding up to some number which cannot be exceeded).
> >>>>>>>
> >>>>>>>Is there a work around?  I would like to be able to generate the results for 
> >>>>>>>all the items at one shot as it speeds the work and precludes human error.
> >>>>>>>
> >>>>>>>Many thanks.
> >>>>>>>
> >>>>>>>
> >>>>>>>
> >>>>>>>"Debra Dalgleish" wrote:
> >>>>>>>
> >>>>>>>
> >>>>>>>
> >>>>>>>
> >>>>>>>
> >>>>>>>>There's sample code here for printing each item in the page field:
> >>>>>>>>
> >>>>>>>>http://www.contextures.com/xlPivot09.html
> >>>>>>>>
> >>>>>>>>You could adapt that to your workbook.
> >>>>>>>>
> >>>>>>>>nrehman wrote:
> >>>>>>>>
> >>>>>>>>
> >>>>>>>>
> >>>>>>>>
> >>>>>>>>>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 underlying cashflow change constantly. 
> >>>>>>>>>
> >>>>>>>>>How could I cycle through the investments and copy the results from the IRR 
> >>>>>>>>>to a separate table? (I can create the copy routine, but have not been 
> >>>>>>>>>successful with the cycling through the pivot table page field). 
> >>>>>>>>>
> >>>>>>>>>Many thanks!
> >>>>>>>>>
> >>>>>>>>
> >>>>>>>>
> >>>>>>>>-- 
> >>>>>>>>Debra Dalgleish
> >>>>>>>>Excel FAQ, Tips & Book List
> >>>>>>>>http://www.contextures.com/tiptech.html
> >>>>>>>>
> >>>>>>>>
> >>>>>>>
> >>>>>>-- 
> >>>>>>Debra Dalgleish
> >>>>>>Excel FAQ, Tips & Book List
> >>>>>>http://www.contextures.com/tiptech.html
> >>>>>>
> >>>>>>
> >>>>>
> >>>>-- 
> >>>>Debra Dalgleish
> >>>>Excel FAQ, Tips & Book List
> >>>>http://www.contextures.com/tiptech.html
> >>>>
> >>>>
> >>>
> >>
> >>-- 
> >>Debra Dalgleish
> >>Excel FAQ, Tips & Book List
> >>http://www.contextures.com/tiptech.html
> >>
> >>
> > 
> 
> 
> -- 
> Debra Dalgleish
> Excel FAQ, Tips & Book List
> http://www.contextures.com/tiptech.html
> 
> 
0
nrehman1 (9)
10/7/2005 3:01:05 PM
The code in one of my sample files may give you some ideas:

   http://www.contextures.com/excelfiles.html

Under PivotTables, look for 'Pivot Page Spinner'


nrehman wrote:
> Progress is sometimes painful.  After trying to decode the routine, I am 
> trying to add two capabilities to the cycling routine, but have had little 
> success (probably due to lack of both knowledge and correct implementation):
> 
> 1)  Include the "All" results 
> Here I would like to include the "All" results for each of the pagefields.  
> I have tried to modify the pivotitem For Next loops to start from "0", but 
> the code did not like this and crashes.
> 
> and 
> 
> 2) Exclude pagefields which create parameters for the data to cycle within.  
> Here I have have tried to modify the pagefield counts in multiple places, 
> but have not been successful.  The idea was that if I could get the pagefield 
> count to be less than the number of pagefields (and the parameter pagefields 
> were placed below the cycling pagefields), then the parameter pagefields 
> would be ignored.
> 
> ANY suggestions?
> 
> Thanks you.
> 
> "Debra Dalgleish" wrote:
> 
> 
>>You're welcome! Thanks for letting me know that it worked.
>>
>>nrehman wrote:
>>
>>>Pure genius.  It's a pleasure to know someone who has mastery of their art.
>>>
>>>A capital thank you!
>>>
>>>
>>>
>>>"Debra Dalgleish" wrote:
>>>
>>>
>>>
>>>>It should work with identical data, if you use UNION ALL in the SQL 
>>>>statement.
>>>>
>>>>nrehman wrote:
>>>>
>>>>
>>>>>Thanks again.
>>>>>
>>>>>I created a new database query (the "Get Data" was trying to link to data on 
>>>>>a directory on your PC when I followed the instructions).  Then created a new 
>>>>>SQL based on you SQL.  This worked, creating a new pivot table which matched 
>>>>>your results.
>>>>>
>>>>>I tried to do the same with some of my data, creating named regions for two 
>>>>>identical worksheets (say using Ontario and Alberta as the worksheet names).  
>>>>>However, I have the curious result of seeing ONLY ONE set of data (from one 
>>>>>named region) when the first column is identical in both of the worksheets 
>>>>>with data.  When I change the first column in the Ontario to be different 
>>>>
>>>>>from the first column in Alberta, I then see the two sets of data.
>>>>
>>>>>Do the first columns have to have different data (?).  And therefore have to 
>>>>>have data that is unique to one worksheet as compared to the other?  Is there 
>>>>>a step that I have missed in consolidating the data which causes identical 
>>>>>data to not be unioned?
>>>>>
>>>>>Thanks!
>>>>>
>>>>>
>>>>>"Debra Dalgleish" wrote:
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>>You could use multiple consolidation ranges, but the results are too 
>>>>>>good. There's an example, and a couple of workarounds, here:
>>>>>>
>>>>>> http://www.contextures.com/xlPivot08.html
>>>>>>
>>>>>>
>>>>>>nrehman wrote:
>>>>>>
>>>>>>
>>>>>>
>>>>>>>Thank you for your reply.
>>>>>>>
>>>>>>>You are absolutely correct.  Creating the identical parameters to cause the 
>>>>>>>same "bug", I found that I had created another variable which was crashing 
>>>>>>>the code.  Once I removed that, the program ran fine (which is really great)!
>>>>>>>
>>>>>>>I will be coming down to another wall soon, which the the maximum number of 
>>>>>>>rows (I have almost 65000 rows by 6 columns of data).  Would you have any 
>>>>>>>ideas for how to work around this issue?  Is there some way in the vb to 
>>>>>>>code/join two worksheets or range two sets of columns for a pivot table?  (If 
>>>>>>>you prefer, I can submit a new item for the forum).
>>>>>>>
>>>>>>>Thanks again for your invaluable help.
>>>>>>>
>>>>>>>Nadir
>>>>>>>
>>>>>>>
>>>>>>>"Debra Dalgleish" wrote:
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>>The page field should allow more than 2000 items. What version of Excel 
>>>>>>>>are you using?
>>>>>>>>
>>>>>>>>
>>>>>>>>nrehman wrote:
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>>>This worked great!  Thank you.
>>>>>>>>>
>>>>>>>>>However, as my sample has now expanded beyond a couple of hundred pivot 
>>>>>>>>>items.  There are now over two thousand unique items and I am faced with the 
>>>>>>>>>error that effectively says there are too many items for the pivotitems 
>>>>>>>>>field.  (I believe this is a function of the number of unique items by the 
>>>>>>>>>columns adding up to some number which cannot be exceeded).
>>>>>>>>>
>>>>>>>>>Is there a work around?  I would like to be able to generate the results for 
>>>>>>>>>all the items at one shot as it speeds the work and precludes human error.
>>>>>>>>>
>>>>>>>>>Many thanks.
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>"Debra Dalgleish" wrote:
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>>There's sample code here for printing each item in the page field:
>>>>>>>>>>
>>>>>>>>>>http://www.contextures.com/xlPivot09.html
>>>>>>>>>>
>>>>>>>>>>You could adapt that to your workbook.
>>>>>>>>>>
>>>>>>>>>>nrehman wrote:
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>>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 underlying cashflow change constantly. 
>>>>>>>>>>>
>>>>>>>>>>>How could I cycle through the investments and copy the results from the IRR 
>>>>>>>>>>>to a separate table? (I can create the copy routine, but have not been 
>>>>>>>>>>>successful with the cycling through the pivot table page field). 
>>>>>>>>>>>
>>>>>>>>>>>Many thanks!
>>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>-- 
>>>>>>>>>>Debra Dalgleish
>>>>>>>>>>Excel FAQ, Tips & Book List
>>>>>>>>>>http://www.contextures.com/tiptech.html
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>
>>>>>>>>-- 
>>>>>>>>Debra Dalgleish
>>>>>>>>Excel FAQ, Tips & Book List
>>>>>>>>http://www.contextures.com/tiptech.html
>>>>>>>>
>>>>>>>>
>>>>>>>
>>>>>>-- 
>>>>>>Debra Dalgleish
>>>>>>Excel FAQ, Tips & Book List
>>>>>>http://www.contextures.com/tiptech.html
>>>>>>
>>>>>>
>>>>>
>>>>-- 
>>>>Debra Dalgleish
>>>>Excel FAQ, Tips & Book List
>>>>http://www.contextures.com/tiptech.html
>>>>
>>>>
>>>
>>
>>-- 
>>Debra Dalgleish
>>Excel FAQ, Tips & Book List
>>http://www.contextures.com/tiptech.html
>>
>>
> 


-- 
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

0
dsd1 (5911)
10/7/2005 6:51:37 PM
Thanks!  I will dig into it.
Regards,


"Debra Dalgleish" wrote:

> The code in one of my sample files may give you some ideas:
> 
>    http://www.contextures.com/excelfiles.html
> 
> Under PivotTables, look for 'Pivot Page Spinner'
> 
> 
> nrehman wrote:
> > Progress is sometimes painful.  After trying to decode the routine, I am 
> > trying to add two capabilities to the cycling routine, but have had little 
> > success (probably due to lack of both knowledge and correct implementation):
> > 
> > 1)  Include the "All" results 
> > Here I would like to include the "All" results for each of the pagefields.  
> > I have tried to modify the pivotitem For Next loops to start from "0", but 
> > the code did not like this and crashes.
> > 
> > and 
> > 
> > 2) Exclude pagefields which create parameters for the data to cycle within.  
> > Here I have have tried to modify the pagefield counts in multiple places, 
> > but have not been successful.  The idea was that if I could get the pagefield 
> > count to be less than the number of pagefields (and the parameter pagefields 
> > were placed below the cycling pagefields), then the parameter pagefields 
> > would be ignored.
> > 
> > ANY suggestions?
> > 
> > Thanks you.
> > 
> > "Debra Dalgleish" wrote:
> > 
> > 
> >>You're welcome! Thanks for letting me know that it worked.
> >>
> >>nrehman wrote:
> >>
> >>>Pure genius.  It's a pleasure to know someone who has mastery of their art.
> >>>
> >>>A capital thank you!
> >>>
> >>>
> >>>
> >>>"Debra Dalgleish" wrote:
> >>>
> >>>
> >>>
> >>>>It should work with identical data, if you use UNION ALL in the SQL 
> >>>>statement.
> >>>>
> >>>>nrehman wrote:
> >>>>
> >>>>
> >>>>>Thanks again.
> >>>>>
> >>>>>I created a new database query (the "Get Data" was trying to link to data on 
> >>>>>a directory on your PC when I followed the instructions).  Then created a new 
> >>>>>SQL based on you SQL.  This worked, creating a new pivot table which matched 
> >>>>>your results.
> >>>>>
> >>>>>I tried to do the same with some of my data, creating named regions for two 
> >>>>>identical worksheets (say using Ontario and Alberta as the worksheet names).  
> >>>>>However, I have the curious result of seeing ONLY ONE set of data (from one 
> >>>>>named region) when the first column is identical in both of the worksheets 
> >>>>>with data.  When I change the first column in the Ontario to be different 
> >>>>
> >>>>>from the first column in Alberta, I then see the two sets of data.
> >>>>
> >>>>>Do the first columns have to have different data (?).  And therefore have to 
> >>>>>have data that is unique to one worksheet as compared to the other?  Is there 
> >>>>>a step that I have missed in consolidating the data which causes identical 
> >>>>>data to not be unioned?
> >>>>>
> >>>>>Thanks!
> >>>>>
> >>>>>
> >>>>>"Debra Dalgleish" wrote:
> >>>>>
> >>>>>
> >>>>>
> >>>>>
> >>>>>>You could use multiple consolidation ranges, but the results are too 
> >>>>>>good. There's an example, and a couple of workarounds, here:
> >>>>>>
> >>>>>> http://www.contextures.com/xlPivot08.html
> >>>>>>
> >>>>>>
> >>>>>>nrehman wrote:
> >>>>>>
> >>>>>>
> >>>>>>
> >>>>>>>Thank you for your reply.
> >>>>>>>
> >>>>>>>You are absolutely correct.  Creating the identical parameters to cause the 
> >>>>>>>same "bug", I found that I had created another variable which was crashing 
> >>>>>>>the code.  Once I removed that, the program ran fine (which is really great)!
> >>>>>>>
> >>>>>>>I will be coming down to another wall soon, which the the maximum number of 
> >>>>>>>rows (I have almost 65000 rows by 6 columns of data).  Would you have any 
> >>>>>>>ideas for how to work around this issue?  Is there some way in the vb to 
> >>>>>>>code/join two worksheets or range two sets of columns for a pivot table?  (If 
> >>>>>>>you prefer, I can submit a new item for the forum).
> >>>>>>>
> >>>>>>>Thanks again for your invaluable help.
> >>>>>>>
> >>>>>>>Nadir
> >>>>>>>
> >>>>>>>
> >>>>>>>"Debra Dalgleish" wrote:
> >>>>>>>
> >>>>>>>
> >>>>>>>
> >>>>>>>
> >>>>>>>
> >>>>>>>>The page field should allow more than 2000 items. What version of Excel 
> >>>>>>>>are you using?
> >>>>>>>>
> >>>>>>>>
> >>>>>>>>nrehman wrote:
> >>>>>>>>
> >>>>>>>>
> >>>>>>>>
> >>>>>>>>
> >>>>>>>>>This worked great!  Thank you.
> >>>>>>>>>
> >>>>>>>>>However, as my sample has now expanded beyond a couple of hundred pivot 
> >>>>>>>>>items.  There are now over two thousand unique items and I am faced with the 
> >>>>>>>>>error that effectively says there are too many items for the pivotitems 
> >>>>>>>>>field.  (I believe this is a function of the number of unique items by the 
> >>>>>>>>>columns adding up to some number which cannot be exceeded).
> >>>>>>>>>
> >>>>>>>>>Is there a work around?  I would like to be able to generate the results for 
> >>>>>>>>>all the items at one shot as it speeds the work and precludes human error.
> >>>>>>>>>
> >>>>>>>>>Many thanks.
> >>>>>>>>>
> >>>>>>>>>
> >>>>>>>>>
> >>>>>>>>>"Debra Dalgleish" wrote:
> >>>>>>>>>
> >>>>>>>>>
> >>>>>>>>>
> >>>>>>>>>
> >>>>>>>>>
> >>>>>>>>>
> >>>>>>>>>>There's sample code here for printing each item in the page field:
> >>>>>>>>>>
> >>>>>>>>>>http://www.contextures.com/xlPivot09.html
> >>>>>>>>>>
> >>>>>>>>>>You could adapt that to your workbook.
> >>>>>>>>>>
> >>>>>>>>>>nrehman wrote:
> >>>>>>>>>>
> >>>>>>>>>>
> >>>>>>>>>>
> >>>>>>>>>>
> >>>>>>>>>>
> >>>>>>>>>>>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 underlying cashflow change constantly. 
> >>>>>>>>>>>
> >>>>>>>>>>>How could I cycle through the investments and copy the results from the IRR 
> >>>>>>>>>>>to a separate table? (I can create the copy routine, but have not been 
> >>>>>>>>>>>successful with the cycling through the pivot table page field). 
> >>>>>>>>>>>
> >>>>>>>>>>>Many thanks!
> >>>>>>>>>>>
> >>>>>>>>>>
> >>>>>>>>>>
> >>>>>>>>>>-- 
> >>>>>>>>>>Debra Dalgleish
> >>>>>>>>>>Excel FAQ, Tips & Book List
> >>>>>>>>>>http://www.contextures.com/tiptech.html
> >>>>>>>>>>
> >>>>>>>>>>
> >>>>>>>>>
> >>>>>>>>-- 
> >>>>>>>>Debra Dalgleish
> >>>>>>>>Excel FAQ, Tips & Book List
> >>>>>>>>http://www.contextures.com/tiptech.html
> >>>>>>>>
> >>>>>>>>
> >>>>>>>
> >>>>>>-- 
> >>>>>>Debra Dalgleish
> >>>>>>Excel FAQ, Tips & Book List
> >>>>>>http://www.contextures.com/tiptech.html
> >>>>>>
> >>>>>>
> >>>>>
> >>>>-- 
> >>>>Debra Dalgleish
> >>>>Excel FAQ, Tips & Book List
> >>>>http://www.contextures.com/tiptech.html
> >>>>
> >>>>
> >>>
> >>
> >>-- 
> >>Debra Dalgleish
> >>Excel FAQ, Tips & Book List
> >>http://www.contextures.com/tiptech.html
> >>
> >>
> > 
> 
> 
> -- 
> Debra Dalgleish
> Excel FAQ, Tips & Book List
> http://www.contextures.com/tiptech.html
> 
> 
0
nrehman1 (9)
10/13/2005 6:32:11 PM
Reply:

Similar Artilces:

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...

Automatic transfer/download match up
From my paycheck, I have $250 withdrawn and placed into a separate bank account. When I download my transactions for this separate bank account, it downloads the $250 transaction and doesn't realize this is the same transaction as the transfer from my main bank account. My question: is there anyway I can force Money to always match this transaction? The way I do it now is to delete the transaction that I downloaded and keep the one that shows as an automatic transfer. Thanks for any ideas! Joey ...

want to do piovt table, where rows over 100,000
Hi.. 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? thank -- Message posted from http://www.ExcelForum.com Hi 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 source -- 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:...

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? ...

Saving a calculated field
First, yes I have read the threads on storing a calculated field and that it is bad mojo to do that. However, I have pay data that I calculate and input to a database and it must be able to be reconciled with our ADP data. So I need the ability to change and fix the data so it does not change as a result of recalculations. I have a form with a field that calculates the pay based on hours and pay rate. I have another field (the "copy" field) next to that one that has the control source set to the database field. I have set the default value of that field to be equal to the...

make a page permanent
I am new to onenote, I have set up some pages that I would like to make permanent. Is there away I can to that? It all depends on what you mean by "permanent." Can you clarify the task you have in mind? -- Thanks, John Guin OneNote Test Team http://blogs.msdn.com/johnguin "musicandbeer" wrote: > I am new to onenote, I have set up some pages that I would like to make > permanent. Is there away I can to that? John, I have made a note book with pages I never want to change, or atleast not very often. Every time I open it all the boxes show u...

How do I display two excel pages at the same time?
I want to have two excel pages displayed on my computer at the same time, but when I open both pages it uses the same master excel and when I toggle between the two it won't open both of them up. I'm using office 2003. Thanks for the help. -- snowtime ------------------------------------------------------------------------ snowtime's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=25982 View this thread: http://www.excelforum.com/showthread.php?threadid=393499 Open both and then do windows>arrange and vertical (or any of the other choices, I usuall...

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. Example: 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...

Access, average several fields in one row
I have several rows of data in a field, I need to average all the entries in one row 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 (field1+field2+field3)/3 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 following. Field: fRow...

How do I get excel files to open automatically from directories?
When I try to open excel files from the directory or from desktop I only get a blank worksheet not the file. I have to then go through File Open to get the file I want. There must be a way to have them open automatically. On Mon, 2 Jan 2006 21:22:01 -0800, Damian <Damian@discussions.microsoft.com> wrote: >When I try to open excel files from the directory or from desktop I only get >a blank worksheet not the file. I have to then go through File Open to get >the file I want. > >There must be a way to have them open automatically. Go to Tools -> Options -> Gen...

Page Number in Repeat Rows area
Is there a way that I can put a Page Number in the Repeat Rows area of a sheet - and get it to update when I print? I have found one piece of VBA that puts a page number within a cell but it is only updated if the cell is outside the Repeat Row area. When it is within the Repeat Rows area I just get a page number of 1 corresponding to the original row location.. I do some VBA programming but am not an expert. TIA cheers Chris Nothing comes to mind that doesn't require VBA code. You can paste the following in the sheet header (File | Page Setup) and page x of y will print on ea...

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 ) { Range currentSentenceRange; Selection sentenceSelection; Sentences sentencesList = m_document.GetSentences(); long sentencesCount = sentencesList.GetCount(); CString replacementCStr(...

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 suggestions? ...

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 ...

Locks up when clicking on links in open web pages
I don't know if I'm posting this in the correct discussion group, and if not, sorry for the trouble. Could you direct me if I'm wrong? The issue is that the internet connection locks up if I click on a link or attempt to print from within an open website. It does not happen each time, but often enough to make me want to pull my hair! I have to shut down the connection - the message says it is 'not responding,' and reconnect, sometimes 2 or 3 times, to be able to use the link from the website. I have a second laptop, and it doesn't have this problem. ...

How do insert name automatically atop of each pg of doc?
Hello, How do I automatically insert name on each page of document like inserting page numbers? My professor told me not to type it in but to insert and I have not been able to do so. This is a new program for me, Word 2007. I have to submit my paper in MLA form and do not wish to lose points because for this. Thanks for your help. Click on the Insert tab of the Ribbon and then in the Header and Footer section, click on Header and make your choice from the options presented or click on the Edit Header button to get a blank header pane in which to insert the name in the...

Update another CPropertySheet page
I have a program which contains 3 CPropertySheet pages. When I add the change the value in Page 1, then it should automatically update a corresponding field in the Page 2. How can I access from one page to another? Thanks. Yan yan wrote: > I have a program which contains 3 CPropertySheet pages. When I add the > change the value in Page 1, then it should automatically update a > corresponding field in the Page 2. How can I access from one page to > another? > > Thanks. > > Yan > > You cannot do that. When you change a value in Page 1 there are cases ...

Outlook 2007: When i Save a New Rule outlook Change it automatically...
Hi people! I have this problem with Outlook 2007. I have 15 e-mails addresses in my outlook, but i can=B4t create the rules for send all the received mails to each emails folder. Example: I want to create a simple rule that move all the e-mail that i get from "example1@example1.com" to the folder "Inbox/ example1@example1.com". When i create the rule, all works fine, i select the correct e-mail account name, the correct destination folder, etc... but when I hit the SAVE button in rules window, Outlook 2007 changes the account automatically to another one. Not automatically...

Insert Page Numbers on Worksheet in Excel 2007
In Excel 2003, if you wanted to put page numbers on multiple worksheets in a workbook, you grouped the worksheets and then added a header or footer, using the page number function. All of your grouped worksheets would shows its own page number. But in Excel 2007, only my first worksheet is numbered when I do this (as Page 1). What's the problem? Do I actually have to put a page number, one by one, on each worksheet? I cannot replicate your problem with 2007. Grouped sheets behave exactly as 2003 did. After grouping and adding a header of Page 1 did you do a print p...

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]) -- Wayne Manchester, England. "J Man" wrote: > I have made a form in which I input different values. On of the values is ...

owa logout show the page cannot be display
The event 36 was triggered when Outlook Web Access (2003) users clicked the "Log Off" shortcut. The OWA user would receive the following message when logging off: "The page cannot be displayed. I try to search on google, some of newsgroup report follow microsoft article Q318239 can fix the issue. but this article can not found on support.microsoft.com. anyone have experience about event 36 OWA logout issue. Please help ...

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? Hi, 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. -- Thanks, Shane Devenshire "excelbanker" wrote: > I am having trouble inserting a data table with legend keys into a line chart > that contains a horizontal target line. Can anyone he...

Evaluate Yes/No Field Based on User Input
Hi. I have a field that is set to Yes/No. I want to ask the user a question and based on their response (whether they type yes or no) I want the query to check the field and return all records marked yes is they type yes and all other records if they type no. How can I do this? Also, could I present them with a simple text box (having yes and no choices) or maybe a check box so they won't have to type anything? If you help me with the first part, this question is a bonus. I'll be happy with just the first question answered. Thanks! A Yes/No field actually stores -...