sanantonio
Board Regular
- Joined
- Oct 26, 2021
- Messages
- 124
- Office Version
- 365
- Platform
- Windows
Hi All,
I'm open to a VBA or formulaic solution.
I have a set of data in Sheet1:
It is the same item in the same location, with 4 different Delivery Dates, it is possible that multiple entries exist for the same date. The way in which the data is delivered to me is such that it cannot be changed, put into a table, filtered, sorted or formulas added to this page. We can pivot from the range into another sheet if this forms part of the solution but no alteration can be made to Sheet1 at all. The length of the data in Sheet1 can vary wildly and in reality will be significantly more rows than my pictured example.
On Sheet2 I need to return the next Delivery Date:
If I do a simple lookup it will of course return the first one it finds, or the last (Depending on how you setup your lookup). - In my example this would return either 12/12/2023 or 19/12/2023.
I could also pivot the data to give me the min and the max - In my example this would return 12/12/2023 and 21/12/2023.
But what I need is the next Delivery date.
As of posting it's the 14/12/2023 so if it were to return me the next delivery date it would return 17/12/2023. It would continue returning this until the 18/12/2023 when it would then next delivery date (In this example 19/12/2023). And then on 20/12/2023 it would return 21/12/2023.
I cannot think of a solution? Again open to VBA or formulaic, whatever will provide a solution
Many thanks in advance!
I'm open to a VBA or formulaic solution.
I have a set of data in Sheet1:
It is the same item in the same location, with 4 different Delivery Dates, it is possible that multiple entries exist for the same date. The way in which the data is delivered to me is such that it cannot be changed, put into a table, filtered, sorted or formulas added to this page. We can pivot from the range into another sheet if this forms part of the solution but no alteration can be made to Sheet1 at all. The length of the data in Sheet1 can vary wildly and in reality will be significantly more rows than my pictured example.
On Sheet2 I need to return the next Delivery Date:
If I do a simple lookup it will of course return the first one it finds, or the last (Depending on how you setup your lookup). - In my example this would return either 12/12/2023 or 19/12/2023.
I could also pivot the data to give me the min and the max - In my example this would return 12/12/2023 and 21/12/2023.
But what I need is the next Delivery date.
As of posting it's the 14/12/2023 so if it were to return me the next delivery date it would return 17/12/2023. It would continue returning this until the 18/12/2023 when it would then next delivery date (In this example 19/12/2023). And then on 20/12/2023 it would return 21/12/2023.
I cannot think of a solution? Again open to VBA or formulaic, whatever will provide a solution
Many thanks in advance!
