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:
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
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.
Bookmarks