List draw and Record of drawings that plot

davidp

New member
Joined
Jan 31, 2017
Messages
11
Reaction score
0
Points
1
Excel Version(s)
2013
Hello,

I am generating a list of drawings, I would also like to have a record of drawings that are plot
I have the next question.
In sheet (List), I have a list of drawings with its title.
In sheet (Search) would like to write in Draw (for example 001) in title would be written automatically, according to the title of the sheet (List)
How can I do it?
View attachment Plan.xlsm
 
Extract numbers from text in one cell

How can I do it?
I did not fully understand you for the 'C' column. I assume that you are putting the text manually (green font in the label attached to the file).
If you want a unique text in the 'C' column, then set this ARRAY formula (the blue text in the label attached to the file).
Code:
=IFERROR(INDEX(LISTADOV;MATCH(0;COUNTIF($C$5:C5;LISTADOV);0));"")
The formula in 'B' column (Draw) is as follows: (This formula extracts the serial number from the text in the 'C' column, after the first dash)
Code:
=IFERROR(LEFT(RIGHT(C6;LEN(C6)-FIND("-";C6));3);"")
The formula in 'D' column (No Sheet) is as follows: (This formula extracts the number from the text in the 'C' column, after the 'H' letters)
Code:
=IFERROR(SUBSTITUTE(MID(MID(C6;FIND("-";C6;FIND("-";C6)+1)+1;256);2;2);".";"";1);"")
The formula in 'E' column (Sheet) is as follows: (This formula extracts the number from the text in the 'C' column, after the 'dot' character)
Code:
=IFERROR(LEFT(REPLACE(C6;1;FIND(".";C6)+0;"");FIND("_";REPLACE(C6;1;FIND(".";C6)+0;""))-1);"")
The formula in 'F' column (No Rev) is as follows: (This formula extracts the number from the text in the 'C' column, at the end of the text or after the 'R' letters)
Code:
=IFERROR(MID(MID(C6;SEARCH("_";C6;1)+1;3);2;3);"")
The formula in 'J' column (Title) is as follows: (I named 'List!B6:E105' range as "Data")
Code:
=IFERROR(VLOOKUP($C6;Data;4;FALSE);"")
Note, I have taken into account that there is a possibility of two digits of each number (green font).

Does help you?
 

Attachments

  • davidp-navic9775.xlsm
    29.2 KB · Views: 5
Back
Top