Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

User input Excel formula

Posted on 2014-01-02
18
Medium Priority
?
637 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 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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

Ever visit a website where you spotted a really cool looking Font, yet couldn't figure out which font family it belonged to, or how to get a copy of it for your own use? This article explains the process of doing exactly that, as well as showing how…
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

885 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