Forum Discussion
Fresh insight to an excel problem
Do you have the most recent version of Excel? Can you enter a formula using the FILTER function, specifically? If so, you should be able to create a formula that would sift through that table you have and use such criteria as the designation of the CP in question, as well as a "window" of date&time using the time stamps of movement....
Another possibility that occurs to me is the Pivot Table with date and time being used as filter(s) for the data to be displayed.
Is it possible for you to post a mock up of your actual file so as to enable me or somebody else here to see the kind of layout of your data? It's hard to give specific suggestions with only a verbal description; and an actual spreadsheet--just not containing proprietary info--is far more helpful than an image.
I wonder if you're going to get a flurry of possible answers. I'm attaching one that makes primary use of the FILTER function, only available in the newest versions of Excel.
There's a cell with yellow background where you can select the code for one of your "current locations" --when you do that, the date below will automatically populate with the latest date for which there's an entry for that location.
Then the list will automatically populate with the rows that correspond to that location and date.
as others have noted, you may need to refine your table to be more specific on actual products...or maybe we're just not seeing what's there in front of us.
Our methods (pregan's pivot table, my use of FILTER) may suffice to give you that "fresh insight" you were seeking, in any event.
