+ Reply to Thread
Results 1 to 6 of 6

Date named ranges not accepting Apr13 eg, having to use _Apr13 but pivot table not ordered

  1. #1
    Registered User
    Join Date
    10-10-2012
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    61

    Date named ranges not accepting Apr13 eg, having to use _Apr13 but pivot table not ordered

    Hi

    Please see sheets attached,

    I am working on a financial year from Apr13-Mar14, im using named table ranges.

    On my RS working sheet, i have 2 data validations, One column A which is the months picked up from the "Do Not Touch" sheet
    The second is column D, which uses the Indirect formula.

    The tables that are linked are found under the sales agents sheet.

    That is all fine, until i try to use a pivot table to display the data. The pivot table will not order the months in the correct order, starts from _Jan14 instead of _Apr13.

    I had to rename the dates to a _mmyy format, after i kept receiving errors when i was saving the workbook saying the name "Apr13" eg is invalid as excel already uses it.

    Also it wont work even If i name the ranges as a mmm-yy as excel keeps reading it as a date which messes with the data validation

    Please help how should I go about this
    Attached Files Attached Files
    Last edited by rikkyshh; 08-22-2013 at 09:55 AM.

  2. #2
    Forum Expert RobertMika's Avatar
    Join Date
    06-22-2009
    Location
    Haverhill, UK
    MS-Off Ver
    Excel 2003-13
    Posts
    1,530

    Re: Date named ranges not accepting Apr13 eg, having to use _Apr13 but pivot table not ord

    Nothing attached...
    If you are http://www.excelforum.com/image.php?type=sigpic&userid=125481&dateline=1392355029happy with the results, please add to the contributor's
    reputation by clicking the reputation icon (star icon).




    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved.
    To undo, select Thread Tools-> Mark thread as Unsolved.
    http://www.excelaris.co.uk

  3. #3
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Date named ranges not accepting Apr13 eg, having to use _Apr13 but pivot table not ord

    Quote Originally Posted by rikkyshh View Post

    I had to rename the dates to a _mmyy format, after i kept receiving errors when i was saving the workbook saying the name "Apr13" eg is invalid as excel already uses it.
    That's because APR13 is a valid cell address in Excel versions 2007 and later.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  4. #4
    Forum Contributor rajeshturaha's Avatar
    Join Date
    08-22-2012
    Location
    Assam, India
    MS-Off Ver
    Excel 2003, 2007
    Posts
    236

    Re: Date named ranges not accepting Apr13 eg, having to use _Apr13 but pivot table not ord

    I think you use APRL13 "mmmmyy" format instead of "mmmyy", will solve your problem. But in PVT it will sort in alphabetical order.
    Last edited by rajeshturaha; 08-22-2013 at 09:51 AM.
    Rgd
    RT
    If my answer(s) helped you, please add me reputation by click on *

  5. #5
    Forum Contributor
    Join Date
    08-14-2013
    Location
    Here and there
    MS-Off Ver
    Excel 2010
    Posts
    376

    Re: Date named ranges not accepting Apr13 eg, having to use _Apr13 but pivot table not ord

    Please read these instructions as issued under FAQ re attaching files:

    How do I attach a file to a post?

    To attach a file to your post, you need to be using the main 'New Post' or 'New Thread' page and not 'Quick Reply'. To use the main 'New Post' page, click the 'Post Reply' button in the relevant thread.

    On this page, below the message box, you will find a button labelled 'Manage Attachments'. Clicking this button will open a new window for uploading attachments. You can upload an attachment either from your computer or from another URL by using the appropriate box on this page. Alternatively you can click the Attachment Icon to open this page.

    To upload a file from your computer, click the 'Browse' button and locate the file. To upload a file from another URL, enter the full URL for the file in the second box on this page. Once you have completed one of the boxes, click 'Upload'.

    Once the upload is completed the file name will appear below the input boxes in this window. You can then close the window to return to the new post screen.

    Cheers

  6. #6
    Forum Contributor rajeshturaha's Avatar
    Join Date
    08-22-2012
    Location
    Assam, India
    MS-Off Ver
    Excel 2003, 2007
    Posts
    236

    Re: Date named ranges not accepting Apr13 eg, having to use _Apr13 but pivot table not ord

    Use format "_yyyymmdd" for name rangeeg _20130401 for april 13 and so on. It will be sorted monthwise in PVT

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Pivot Table Using Multiple Named Ranges
    By nathanB in forum Excel General
    Replies: 2
    Last Post: 11-26-2012, 08:59 AM
  2. Pivot Table Date Ranges
    By william. in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 09-05-2012, 04:24 PM
  3. Replies: 0
    Last Post: 03-30-2012, 01:56 PM
  4. Applying named ranges to Pivot table
    By RBI in forum Excel General
    Replies: 5
    Last Post: 10-07-2008, 01:28 PM
  5. Pivot table page filter not accepting multiple values. Workaround?
    By jco in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-25-2005, 05:05 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1