List folder path according to folder index

There are two work sheets, option 1 and Option2.
We are still in designing stage.

“Option 1” is a folder structure
“Options 2” has list of files, these will go to “option 1” folders.

As you can see in “option 1” work sheet, each folder name has its index number too.

What I want to do is, if I enter a folder index number in D column (NEW_ID) of the option 2 work sheet, the folder path should be displayed in the E column (NEW_PATH) of the option 2.


For example:

If I type 3.21.01.01.01, the path should be


Orange>working> 50 Technical Data Package> 3.21 Revised Function and PerBookance Specifications >3.21.01 harbour Definitions>3.21.01.01 kamart-001>3.21.01.01.01 document

I don’t mind if the path without index number .

3.21.01.01.01 is an index number of the folder and the name of the folder is Document.

Example 2

If type 3.15 in column D of option 2 it should give path structure as follows in column E (NEW_PATH) of option 2.

Orange>working>03IMPLEMENTATION PHASE>40 Test and Evaluation>3.15 Test and Evaluation Master Plan


Option 1 folder structure can extend as required such as 3.21.01.01.01. (5 levels)
I am not intending to go deeper than 5 levels.


So It can be two level, three level, 4 levels or five levels such as 1.01, 1.01.01, 1.01.10.02 or 1.01.10.02.01 etc.

As you can see above I am going to store files E column onwards.


Looking for a smart solution..