Solved

# User input Excel formula

Posted on 2014-01-02
Medium Priority
648 Views
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
Question by:Kdankwah
• 10
• 8

LVL 23

Accepted Solution

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

ID: 39751677

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

Thanks
0

LVL 23

Expert Comment

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

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

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

Author Comment

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

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

ID: 39754600
Ok then.
0

Author Comment

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

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

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

ID: 39754768
I rather want to use the PV for summary.
0

LVL 23

Expert Comment

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

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

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

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

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

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

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.