+ Reply to Thread
Results 1 to 1 of 1

Timeline Slicer Not Working with Blanks from Date Formula

  1. #1
    Registered User
    Join Date
    12-11-2019
    Location
    Arizona, USA
    MS-Off Ver
    2016
    Posts
    2

    Timeline Slicer Not Working with Blanks from Date Formula

    So I have a report I'm going to run often, and I want to:

    1) Dump it into a "pasteData" sheet in excel
    2) Have a "TranslateData" sheet that converts the pasted data a little bit (i.e., a match/index list to consolidate variations on names, rename codes to words, etc)
    3) Have a pivot table built off the translated data sheet

    So I have
    TranslateData A2:
    Please Login or Register  to view this content.
    and so on.

    Basically each row first checks if there is data on the PasteData sheet, and if so, copies it over to TranslateData. (t

    One of those fields is a date field that I want to make a timeline slicer off of, but if I have a row where the code above resolves to blank, then Excel reads it as text or something and consider that column to not be a date field, preventing me from using a timeline slicer.

    What I WANT is to have the translated sheet's formulas dragged down 10k rows or so, so it always pulls every row from the pasted sheet (~8k rows) without missing any, and then the excess rows all resolve to blanks.

    If I remove the blank formula rows, then the slicer can be added just fine - but I'm building this report for some people that I can't expect to understand how to extend/reduce all these formulas every time they run it, so I'd really like a way to make this work so they can just:
    Run report, paste report, refresh the pivot table, and then be done and able to mess with the slicers.



    Is there a way around timeline slicers seeing a formula that resolves to blank the same as an actually blank cell?


    My two other options are instead of formatting them as dates, I could
    Please Login or Register  to view this content.
    and then do a regular slicer and pretend it's a timeline slicer...

    ....or program a macro that translates the pasted data into what I want, so every time it runs, it'll be the right amount of rows.
    Last edited by dkramer_AZ; 01-10-2020 at 04:39 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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