Rolling 30 days

[wzm108it]

Is there a way to have excel take a massive spreadsheet of scheduled dispatches from one sheet, and only show the last 30 days in another sheet in the same workbook? We want to have the historical data and ability to still add new dispatches, but for ease of the current ones in process, it would be easier to have the last 7, 14, or 30 days on a separate sheet that loads quicker.

[Pete_UK]

Yes, this could be done with a few formulae. I presume the historical data would be in a separate workbook, which won't necessarily be open at the same time as the file that displays the last 30 days.

Attach a sample workbook showing how your data is laid out and how you want it to appear in the other sheet, then I can set it up for you.

Pete

[Pete_UK]

In the meantime, you might want to take a look at this thread from a few days ago, which was on a similar topic:

http://www.excelforum.com/excel-form...workbooks.html

Hope this helps.

Pete

[wzm108it]

Attached is the example. It would actually be in the same workbook that would be opened. The dispatch report has just gotten to be over 1,000 lines and takes forever to scroll and is growing. So having a shortened list would make it easier to work and view. Both sheets would have the same exact information. Just one would be all of the data, and the other would be the last 7, 14, or 30 days.


Example.xlsx

[Pete_UK]

So which date column would be used to determine if it is within the last 30 days?

Do you want to have a drop-down to select 7 or 14 or 30 days, and the display adjusts automatically?

It would be helpful to have a sample with a few more records, some within and some outside those days, so I can test it more thoroughly.

Pete

[wzm108it]

Column D would be the date that would need to be used.

A drop down would be great! I provided an updated sheet with some more data, and the sheet with just the last 30 days. I did remove the top header from the Recent tab since it's not needed in the recent tab.

[Pete_UK]

In the Data sheet of the attached file I have inserted a new column A and put this formula in A3:

=IF(AND(INT(E3)<=Recent!$C$1,INT(E3)>Recent!$C$1-Recent!$C$2),COUNTIF(A$2:A2,">0")+1,"-")

which is copied down beyond your data. This formula just sets up a unique sequential number for those records that fall within the date range specified on the other sheet, and returns a hyphen if the record doesn't match the criteria - these indicate where the formula is active. I've copied to row 100, but if you have 1000 records in your real file you need to copy it down beyond that (it won't do any harm if you copy it down to row 5000, for example). This column can then be hidden if you want the sheet to look as it did before.

On the Recent sheet I've also inserted a new column A, but in addition I've inserted two blank rows at the top. This allows you, in C2, to select how many days you want to see using the drop-down (7, 14 or 30). I've also put a formula in C1 which picks up the last recorded date in the Data sheet. I felt this was better than just using =TODAY(), as the data may not be totally up to date. Using this formula enables you to see the last 30 days (or whatever) of entries, rather than 30 days from today (which might only be 28 entries if you are 2 days behind).

This formula is in A4:

=IFERROR(MATCH(ROWS($1:1),Data!A:A,0),"-")

and this finds the row number on the Data sheet where the first identified record occurs. As this is copied down it finds the second record, then the third, and so on. Cell B4 contains this formula:

=IF(OR($A4="",$A4="-"),"",IF(INDEX(Data!B:B,$A4)="","",INDEX(Data!B:B,$A4)))

and this is copied across to cell AD4. It retrieves the data from column B of the Data sheet from the row given in column A, and as it is copied across it gets data from the other columns. I've applied the same formatting as you had before, then this row of formulae can be copied down as far as you need them - I've copied to row 50, but if you only have one entry per day, as your example data suggests, then you won't need all those. You can hide column A, as before, if you like.

So, just use the drop-down in C2 to select how many days you want to see, and the data will change automatically.

Hope this helps.

Pete

P.S. I used your Example2 file to put this together - not sure what the difference is.

[wzm108it]

THANK YOU! I spent two days last week trying to find something to make this work. The only difference between 2 and 3 is 3 had the data down to 30 days to give an example! I will take this and incorporate this into the real sheet in the next couple days. I appreciate it!

[Pete_UK]

You're welcome.

If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Also, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete