Forum Discussion

Copper Contributor

Oct 03, 2021

Solved

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 04, 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

Yea_So

Bronze Contributor

Oct 04, 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 04, 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 04, 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 04, 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.
  - Yea_So
    Bronze Contributor
    Oct 04, 2021
    vicarma
    
    it is better to work off of the raw data that the pivot table is getting its values from, from the get go. can you post the raw data?