Solved

User input Excel formula

Posted on 2014-01-02
18
615 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
 
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Article by: Leon
Software Metering within our group of companies has always been an afterthought until auditing of software and licensing became a pain point. Orchestrator and SCCM metering gave us the answer and it was an exciting process.
Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

760 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now