How do I deal with necessary gaps in Pivot Table source data?


New Member
Mar 19, 2017
Hi there

I've created a formula whereby a column will post an end of week Friday date into a cell if a threshold is met. If however the threshold is not met, the column will post a blank into that column's row. There has to be a row spacing of five working days to each Friday as there are calculations on each day which are needed for the formula to sum that Friday's threshold.

On Pivot Tables, it will list all the dates that are over the threshold perfectly in the Pivot Table Row Field without blanks. However, because the dates are not in every cell on the source data, I cannot get the data to group so I can carry out a seasonal analysis. The Group Field is greyed out.

If I copy and paste the data to a separate column removing the gaps, all is fine - the Group Field becomes active. However, this is an impractical solution as the data has to be constantly updated and I have 60 separate columns (!) which need the same treatment every Friday.

Thank you to anyone who has a solution that can save my life!

Forum statistics

Latest member

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...