Replies

I think it would help if I knew how to use it. Here is the line of code I used:

Worksheets(“Data Table”).Range(“A5”).Formula = Worksheets(“Data Table”).Range(“D15”)

This gives me the value from D15 in A5, still not the formula as string. What am I doing wrong?

I’ll try your suggestion tomorrow as I have to run now. I thank you again for your help and your insights.

I’ll let you know how it all comes out.

Regards,

John,

Your routine works perfectly. Thanks so much.

Regards,

Steve,

Your suggestion didn’t work. I get “Assignment to Constant nor Permitted” errors. When I comment out the Dim (I had dimmed RInput as Range), that error goes away, but then I get a run time error that says “Object required”.

Any ideas?

Thanks,

Thank you very much. I just could not see it.

I had never heard of NUMSONLY either. It was presented to me as a function that comes with the Analysis Toolpak, but after I read your post I went to the MS Technet website and found a list of functions that do come with the Analysis Toolpak and NUMSONLY was not one of them.

There are a number of XLA files that are password protected that load with Excel in this corporate setting, so my guess is that one of them does have NUMSONLY as a User Defined Function.

Thanks for your reply.

I do a lot of work with pivot tables but I don’t have a quick answer for this problem. I wouldn’t mind taking a look at it if are able to post it.

My “Brook” is short for Brooklyn, which is where I call home.

Glad I could be of some assistance.

I have attached a modified copy of your file with my solution. Basically I created an IF with an OR and a series of ANDs, and I entered the date ranges you mentioned in columns C and D. Otherwise you’d have to use something like the DATE function in the formula, making it more lengthy and more complex.

Hope this helps,

I use pivot tables a lot in my job and I am familiar with this message. Basically, there is a limit to the number of items that Excel will place in either a row or a column field. The only remedy if you exceed these limits is to convert one or more row/column fields into page fields. Here is a link to the Microsoft Technet website that shows their Knowledge Base article “Limits of Pivot Tables in Microsoft Excel 2000”:

http://linkURLhttp://support.microsoft.com…US;Q211517&%5B/url%5D .

I can tell you from experience that I have pivot tables that generate that message in Excel 2000, but the same tables run fine in Excel XP.

Basically, your choices are: (1) reduce the number of row/column fields in your table; (2) use page fields wherever possible instead of row/column fields; (3) upgrade to XP.

Good luck,

Thanks for your reply. Your formula works perfectly, as does Colin’s array formula . I think I’m going to have to study the SUMPRODUCT formula in some detail.

Thanks,

Thank you for your response. I am really pleased to tell you that your formula works perfectly.

Regards,

Yet another approach (which we use where I work) is:

1. Create new column.
2. If your new column is E, E1 contains formula “=D1” (without the quotes).
3. Format the new column as “mmm/yy”.
4. Copy–> Edit Paste Special–> Values.

You’ll get the same result for your pivot table.

Finally, we also use VBA code to be sure the months appear in the correct order. Here’s a sample of our code (keeping in mind that we call this field “Month” and we use only the first three letters of the month without the year):

On Error Resume Next
With .PivotFields(“Month”)
.PivotItems(“Jan”).Position = 1
.PivotItems(“Feb”).Position = 2
.PivotItems(“Mar”).Position = 3
.PivotItems(“Apr”).Position = 4
.PivotItems(“May”).Position = 5
.PivotItems(“Jun”).Position = 6
.PivotItems(“Jul”).Position = 7
.PivotItems(“Aug”).Position = 8
.PivotItems(“Sep”).Position = 9
.PivotItems(“Oct”).Position = 10
.PivotItems(“Nov”).Position = 11
.PivotItems(“Dec”).Position = 12
End With
On Error GoTo 0

We need the error trapping because not all of our reports contain all the months.

Hope this helps.

Regards,

Thanks for the suggestion; I will pass it along to the user and see if he is interested in such an approach.

I’m curious about file size with this approach. If the user ultimately has as many as 80 or 90 files (or more), how would that affect the file size of the master file? Any ideas?

Thanks again for the suggestion.

Regards,

Well, here’s a kind of klutzy but effective way to do it. Have your user create two print areas and store each of them as Views. The first print area can be stored with the repeating rows and the second print area can be stored without the rows. Then use Report Manager to create a print report that will print both print areas in a single report (even with consecutive page numbers, if the user wishes). The thing to remember about using Views is that when you store a print area you also store the page setup settings, so it is necessary to make sure the user first defines the print area correctly, then correctly sets the page setup settings, before saving as a View.

Hope this helps.