Forum Discussion
Calculate date in a specific product list
For that layout
it could be
Product:
=INDEX(FILTER(C:C, C:C <> ""),XMATCH($G$2, FILTER(C:C, C:C <> "") )+$G$3+$G$4)Date:
=INDEX(A:A, XMATCH(0, C1:INDEX(C:C, XMATCH( G7,C:C))+0,0,-1) )
For that layout
it could be
Product:
=INDEX(FILTER(C:C, C:C <> ""),XMATCH($G$2, FILTER(C:C, C:C <> "") )+$G$3+$G$4)Date:
=INDEX(A:A, XMATCH(0, C1:INDEX(C:C, XMATCH( G7,C:C))+0,0,-1) )
Wonderful, that works. Just had to change all the ',' to ';'
The list is in another excel sheet. How do I change the path from C:C to Sheet1!C:C ?
Exactly as you said
=INDEX( FILTER( Sheet1!C:C, Sheet1!C:C <> ""), XMATCH( $G$2, FILTER(Sheet1!C:C, Sheet1!C:C <> "") ) + $G$3+$G$4 )and
=INDEX( Sheet1!A:A, XMATCH( 0, Sheet1!C1:INDEX( Sheet1!C:C, XMATCH( G7, Sheet1!C:C))+0, 0, -1) )Please check in second sheet attached.
The only I'd do not use entire columns in formulas - better named ranges, dynamic ranges or simple range with some gap for future expansion.
- Thank you for your help. Do you have any solution for:
How do I calculate the number of productstart that day? So if the product number is the 3rd start in the list 2021-10-02, I want the function to return only '3'. SergeiBaklan Thank you very much, very helpful.
How do I calculate the number of start that day? So if the product number is the 3rd start in the list 2021-10-02, I want the function to return only '3'.
And I discovered another issue. I have made dropdown menus in my table. When I expand (adding a row) the dropdown doesn't follow, also the borders I've made. How do I change that?
And this changes when I am expanding the table, in a way that I do not want it to change.
=INDEX(Startprogram!A:A; XMATCH(0; Startprogram!C5:INDEX(Startprogram!C:C; XMATCH( [@[Product no]];Startprogram!C:C))+0;0;-1) )
BR