Sep 30 2021 12:13 AM - edited Sep 30 2021 02:00 AM
Hi,
I would need to be able to calculate a shortage date/time. Attached document represents all assembly product starts in a factory. Based on what the user/vlookup will enter in G1-G4, I would like to return the product number in this list and the start position of the day .
As an example, the user enter a shortage: 111112
So with 7+5 positions ahead I would like this script to return:
Date: 6/10
Product: 111116
1:st product start of the day.
I guess that one solution could be a VBA that counts down the column only where format is "XXXXXX".
(And the layout of the excel product list can unfortunately not be changed/redesigned).
Sep 30 2021 04:45 AM
Please describe in more detail what your plans are.
Welcome to your Excel discussion space!
Thank you for your understanding and patience
Nikolino
I know I don't know anything (Socrates)
Sep 30 2021 05:09 AM - edited Sep 30 2021 05:11 AM
@NikolinoDE Hi,
G2 = 111112
This is the product I want to start the calculation from. Then calculate 7+5 (G3 + G4 number of steps) down the list and return the new product number, date and number of productstart of that day.
G columns are where the user will enter the data.
Sep 30 2021 07:31 AM
SolutionFor 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) )
Sep 30 2021 10:21 AM
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 ?
Sep 30 2021 01:39 PM
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.
Sep 30 2021 10:26 PM - edited Sep 30 2021 10:38 PM
@Sergei Baklan 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
Oct 04 2021 10:45 PM
Sep 30 2021 07:31 AM
SolutionFor 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) )