I have data in a pivot chart as follows:
I need to be able to type a min and max Date range (corresponding to Posting Date Column) in the excel sheet and this needs to then filter the pivot table (let me know if I need to Paste Values in another sheet) to show only the Dates within the range given.
- If you need clarification, ask it in the comment box above.
- Better answers use proper spelling and grammar.
- Provide details, support with references or personal experience.
Tell us some more! Your answer needs to include more details to help people.You can't post answers that contain an email address.Please enter a valid email address.The email address entered is already associated to an account.Login to postPlease use English characters only.
Tip: The max point reward for answering a question is 15.
Microsoft Excel is a commercial spreadsheet application . It features calculation, graphing tools, pivot tables, and a macro programming language called Visual Basic for Applications.
Microsoft Excel has the basic features of all spreadsheets, using a grid of cells arranged in numbered rows and letter-named columns to organize data manipulations like arithmetic operations. It has a battery of supplied functions to answer statistical, engineering and financial needs. In addition, it can display data as line graphs, histograms and charts, and with a very limited three-dimensional graphical display.
There are a couple of types of spreadsheet - Depending on the size of the data you are working with - filter may the quickest option or Query report.
Your worksheet will contain all your data i.e. Customer, contact no, outstaning actions and due date - You can create a filter aon this set criteria on the due data - as less than or equal to todays date - which will only display your outstanding actions required by today or earlier.
Another option if you have installed MS Query, is to create a seperate report - again using the criteria of due date being less than or equal to today.
Producing this from your existing data is much easier than trying to re-create your data - But I can send you how I would create this sheet if the above explanation is not easy to follow - If you let me have your email address.
My suggestion is to use a Pivot Table. Follow these instructions:
Click on column A1 - then in the menu bar click DATA > Pivot Table and Pivot Chart Report.
Click NEXT on step 1 of pivot table wizard.
For step two you will want to highlight all of the data on your sheet. then click next.
Choose NEW WORKSHEET for step 3 of the pivot table wizard and click finish.
Once the Pivot Table sheet opens, drag the STATUS field into the top left corner of the pivot table view, where "drop column fields" and "drop row fields" meet. Next drop another instance of the STATUS field into the "drop row fields here" column. And lastly drop the OWNER field into the "drop column fields here" section. You should now be viewing a report showing you exactly what you were looking for.
Once your data has been input into Excel - I assume you can do this via importing an outstanding debtors deport.
The easiest way I can see is by Pivot Table.
Your data should include open invoices and their respective due dates, it will be easier if you could covert these due dates into months - Create a pivot entering customer, outstanding values as your data and due months as your range.
Suppose you have a Database regarding sales of this Month inthe Column and Row Fromat with column Heading SalesPerson, Company, Product and Amount. where salesperson sales to different company different Products more than once. In this type of Database Salesperson, Company and Products appears more than once. Now you can use PivotTable to view and analyze your data in different Formats like Salesman wise COmpany wise and Productwise with different formatting REports with a facility of Preparing Charts too.
You need to create subtotals so that it looks like something like this:
Prod/units Amt. Level Total
xxxx 10.00 1 yyyy 20.00 1 30.00
dddd 40.00 2 ffff 30.00 2 70.00
To do this I would suggest using the Pivot Table feature of Excel. It is not too hard to use but you do have to know what you want and also have to be sure you are getting results as expected. Like any tool, it has to be used right. You can also use sub-total functions supplied in VBA as well in your cells.
If you want to avoid using VBA functions in cell and deal with ranges, etc., use the Pivot Table Wizard.
Some reading: http://www.ehow.com/how_13020_pivot-table-wizard.html http://www.microsoft.com/dynamics/using/excel_pivot_tables_collins.mspx http://www.ozgrid.com/Excel/excel-pivot-tables.htm
Also Google "Excel pivot table wizard" to get more.
Highlight the entire pivot table and name it as a range. Then in the formula, replace the reference to the table addresses with the range name. Then wherever you move the table (move, not cut and paste) it will automatically gather the right data. Thanks for rating FixYa!