Solved

User input Excel formula

Posted on 2014-01-02
18
620 Views
Last Modified: 2014-01-14
Happy New Year all,

I need to be able to let users input date ranges on an excel pivot table that I am designing.  I have attached a snapshot of the test data.  What I want is an excel formula that will for example allow users to input a date range of  say December or any month within the period column, if a user inputs a date range,excel should summarize the year to date  and month to date for each of the categories.

Thanks
user-input-tester.xlsx
0
Comment
Question by:Kdankwah
  • 10
  • 8
18 Comments
 
LVL 23

Accepted Solution

by:
NBVC earned 500 total points
ID: 39751636
You can create a Dynamic Named Range to use for the Pivot Table.

Assuming user enters a date in, say F2, representing any date in the last month you want included, go to Formulas, Define Name,

Enter a name like: DateRange
Enter a formula: =OFFSET('User input tester'!$A$1,,,COUNTIF('User input tester'!$C:$C,"<="&EOMONTH('User input tester'!$F$2,0))+1,4)

Click Ok.

Now with a date in F2, go to any cell in the table, and then Insert|Pivot Table, set up the PT as you desire.

When you change the date in F2, and refresh the table you should get the sums to date.
0
 

Author Comment

by:Kdankwah
ID: 39751677
Thanks for your prompt reply.

Can I use the same formula to sum the amount based on the categories?

Thanks
0
 
LVL 23

Expert Comment

by:NBVC
ID: 39751699
For MTD:

=SUMIFS($B$2:$B$31,$C$2:$C$31,">="&EOMONTH(F2,-1)+1,$C$2:$C$31,"<="&EOMONTH(F2,0))

For YTD:

=SUM(B2:INDEX($B$2:$B$31,COUNTIF($C$2:$C$31,"<="&EOMONTH(F2,0))))
0
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
LVL 23

Expert Comment

by:NBVC
ID: 39751720
If you redefine the Dynamic Named Range as:

=OFFSET('User input tester'!$A$1,,,COUNTIF('User input tester'!$C$1:$C$31,"<="&EOMONTH('User input tester'!$F$2,0))+1,4)

Then you can use the named range in the formulas:

MTD:

=SUMIFS(INDEX(DateRange,0,2),INDEX(DateRange,0,3),">="&EOMONTH(F2,-1)+1,INDEX(DateRange,0,3),"<="&EOMONTH(F2,0))

YTD:

=SUM($B$2:INDEX(INDEX(DateRange,0,2),COUNTIF(INDEX(DateRange,0,3),"<="&EOMONTH(F2,0))))

or you can move the sums away from the bottom of the table, and keep the DNR the same as it was.
0
 

Author Comment

by:Kdankwah
ID: 39751732
Thanks will try the formulas and let you know the outcomes asap.
0
 

Author Comment

by:Kdankwah
ID: 39754534
When I insert the MTD formula in the pivot table calculated field I get the error:
"References, names and arrays are not supported in Pivot table formulas"

Thanks
0
 
LVL 23

Expert Comment

by:NBVC
ID: 39754554
Those aren't meant for inserting into the Pivot Table,

You can group the PT by Month and then use Subtotals to get the MTD values and the Grand Total to get YTD.
0
 

Author Comment

by:Kdankwah
ID: 39754600
Ok then.
0
 

Author Comment

by:Kdankwah
ID: 39754721
Would you happen to know of anyway I can do the MTD and YTD caluculations in a pivot table?  I tried the grouping in PV but it does not do it the way I want.

I did a workaround on it as per the attached worksheet by inserting a column called Curr month in the dataset and adding the current amount data in that column, I also added the same to the YTD column and then summed the Curr Month as MTD in PV table.

I thought this way is rather cumbersome to do on a monthly basis as an update to the dataset.  
I would rather use a formula with less user intervention.  

What do you think?  Any help will do.

Thanks
Monthly-report.pdf
0
 
LVL 23

Expert Comment

by:NBVC
ID: 39754747
What's wrong with using the formulas I gave you on the main data, and use the PT for details?
0
 

Author Comment

by:Kdankwah
ID: 39754758
Bear with me, please.  Can you do it using the excel data I attached?  I dont seem to get it to work for me.
0
 

Author Comment

by:Kdankwah
ID: 39754768
I rather want to use the PV for summary.
0
 
LVL 23

Expert Comment

by:NBVC
ID: 39754792
Ok, see attached.

I amended slightly the YTD formula as it was off by 1 cell in the included range...
Copy-of-user-input-tester.xlsx
0
 
LVL 23

Expert Comment

by:NBVC
ID: 39754799
Here is another version with the summaries in the Pivot Table,

You can move around the categories, etc, as desired.
Copy-of-user-input-tester.xlsx
0
 

Author Comment

by:Kdankwah
ID: 39754825
I wanted 2 columns on the PV called MTD and YTD and this should be based on the each category summary on the PV
0
 
LVL 23

Expert Comment

by:NBVC
ID: 39754858
In order for the YTD to show, the PT needs to display all the months up to the YTD month.

You can filter out the other months, if desired via the drop down for the month category.\

See attached...
Copy-of-user-input-tester.xlsx
0
 

Author Comment

by:Kdankwah
ID: 39754877
Oh I see, but I download the raw data  every month with the category, invoice numbers, account, depts etc so my intention was to update the dataset every month.  The data set field names are always the same, I guess I have to break the dataset by month and then sum all ofmit to get the year to date.  I will get back to you on this.  I get the idea now.
Thanks
0
 

Author Comment

by:Kdankwah
ID: 39759344
I got the PV working for the current year.  I was just thrown another wrinkle in the whole problem.  It is to do with Previous Years Info.  

The columns will be the same except that the Previous years info will be for the whole year.  How do I get the system to give me only current year's month and also get the prev years month.  For example:  If I am doing a PCV on December 2013, I need to be able to show the data for Dec 2012 in the PV table.

My problem is how to get them side by side.

Thanks
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question