Forum Discussion

Copper Contributor

Oct 03, 2021

Providing an Availability Date

Hi, I have a task to provide the availability date for confirming Customer Orders. I have built an initial excel table with all the Demand (Customer Sales Orders) and Supply (Purchase Orders), t...

Promised Date test file (031021).xlsx12 KB

excel

Formulas and Functions

Macros and VBA

Yea_So

Oct 05, 2021

vicarma

something like this?

The main formula is:

=INDEX(
       $C5:$H$30,
       MATCH(
             0,
             FILTER(
                    $H5:$H$30,
                    $C5:$C$30=C5
             ),
             0
       ),
       2
 )

The rest of the formula is just to format the date

cheers

Vicarma Promised Date test file (031021).xlsx13 KB

vicarma

Copper Contributor

Yea_So

Many thanks for this and the rapid reply - it works really well, and the use of FILTER in the INDEX/MATCH is really clever.

As I mentioned in the original post, the result table is coming from a pivot table, so the length of the table can vary; is there a possibility to modify the formula so that it is dynamic for the last row (particularly if the result table is extended)?

Many thanks once again for your help.

Cheers

Yea_So

Bronze Contributor

Oct 05, 2021

vicarma

If its an excel formatted table, the formulas should propagate dynamically (this is a conjecture on my part since I've seen it happen) test it and let me know so we can figure it out together.

vicarma
Copper Contributor
Oct 05, 2021
Yea_So

Sorry, my bad. It's not a table, as I am calculating directly from the pivot table result.

Hope this helps to understand the reason for the "growing" table length.

Cheers
- Yea_So
  Bronze Contributor
  Oct 05, 2021
  vicarma
  
  can you elaborate on: as I am calculating directly from the pivot table result.
  I know you cannot put a formula within the pivot table so you must mean you're getting the pivot data from the pivot table into another table. Is my assumption accurate or not?
  - vicarma
    Copper Contributor
    Oct 05, 2021
    Yea_So
    
    Sorry - poor definition (again) from me.
    
    From the example file provided, the pivot table would be in columns C:G and then I am adding the formula to column I. Therefore, when the pivot table is refreshed, there is a possibility that it will have more rows (and then the last fixed reference in the formula would need to be amended).
    
    Hope this helps to explain better.
    
    Cheers.

Forum Discussion

Providing an Availability Date

Share

Resources