Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

User input Excel formula

Posted on 2014-01-02
18
Medium Priority
?
631 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 8
18 Comments
 
LVL 23

Accepted Solution

by:
NBVC earned 2000 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
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

I was prompted to write this article after the recent World-Wide Ransomware outbreak. For years now, System Administrators around the world have used the excuse of "Waiting a Bit" before applying Security Patch Updates. This type of reasoning to me …
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

721 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