I want to return the value in column A based on the value in column C being (value in current row column C)-1.
There could be up to 2000 rows, so a basic 'nested if' won't be anywhere near enough.
I know it probably involves Do Until, but I'm incredibly rusty on macros - but on a time-pressure from work to complete the task. Any assistance much appreciated!
The attached file is a simplified example, tweaked from a manufacturing Bill of Materials (BOM).
Column B has a block of 8 digits to visually represent the line number and sub-levels, as per the manufacturing software. I've converted it to a level number in column C.
Formula/macro is required for each cell in column D.
For example, to derive cell D21:
Cell C21 is at level 4.
Look to each row above until you reach level 3. In this instance, the first level 3 it meets above C21 is in C16.
Result is contents of A16, ie P082A79098WE.
Disregard any other level 3's higher up the BOM.
Disregard completely if level = 1.
Thank you!