Home

Create table from 3 types of variables within a cell that contains a dynamic alpha numeric range

%3CLINGO-SUB%20id%3D%22lingo-sub-643217%22%20slang%3D%22en-US%22%3ECreate%20table%20from%203%20types%20of%20variables%20within%20a%20cell%20that%20contains%20a%20dynamic%20alpha%20numeric%20range%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-643217%22%20slang%3D%22en-US%22%3E%3CP%3EI%20dont%20know%20how%20best%20to%20explain%20my%20scenario.%3C%2FP%3E%3CP%3EImported%20data%20will%20have%203%20types%20of%20variables%20in%20each%20cell.%20A%20range%20has%20to%20be%20identified.%20labels%20assigned%20to%20cells%20based%20on%20that%20range%2C%20populate%20cells%20in%20that%20range%2C%20and%20change%20color%20of%20cells%20in%20that%20range.%3CBR%20%2F%3E-Type%201%20used%20to%20identify%20fill%20color%20of%20cell%20identified%20in%20Type%202(%24PI%3DRED%2C%24TI%3D%20NO%20COLOR%2C%20%24SI%3DGreen)%3CBR%20%2F%3E-Type%202%20Range%20of%20cells%20dynamically%20identified%2C%202%20characters%20long.%20The%20first%20character%20will%20be%20a%20letter%20the%20second%20will%20be%20number.%20Letters%20start%20with%20A%2C%20numbers%20start%20with%201%20and%20end%20in%200.%20examples%20A1-A0%2C%20A2-A2%2C%20A1-C4.%20each%20grouping%20of%20data%20will%20have%20different%20set%20of%20ranges%26nbsp%3B%3C%2FP%3E%3CP%3E-Type%203%20will%20be%20the%20data%20for%20each%20cell%20example%20C%3D1%20C%3DY6DP%20C%3DRV3S%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20will%20have%20to%20have%20Multiple%20tables%20on%20one%20sheet%20so%20i%20can't%20use%20the%20cells%20row%20and%20column%20as%20the%20Type%202%20data%26nbsp%3B%20ie..%20type%202%20data%20of%20A3-G4%20can't%20refer%20to%20cells%20A3-G4%20on%20the%20sheet.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20is%20what%20outcome%20should%20look%20like%26nbsp%3B%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3EINIT%3C%2FTD%3E%3CTD%3ECLASS%3C%2FTD%3E%3CTD%3EGROUP1DAY%3C%2FTD%3E%3CTD%3ECLASS%3C%2FTD%3E%3CTD%3EGROUP1NITE%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EA1%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E%24TI(A1)%2CC%3D1%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E%24PI(I1-I5)%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EA2%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E%24TI(A2-A2)%2CC%3D1%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%3C%2FTD%3E%3CTD%3E5%3C%2FTD%3E%3CTD%3E%24PI(F7-H6)%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EA3%3C%2FTD%3E%3CTD%3E3%3C%2FTD%3E%3CTD%3E%24TI(A3-A4)%2CC%3D3%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%3C%2FTD%3E%3CTD%3EC%3C%2FTD%3E%3CTD%3E%24TI(A1)%2CC%3D1%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EA4%3C%2FTD%3E%3CTD%3E3%3C%2FTD%3E%3CTD%3E%24TI(A5-A6)%2CC%3DY6DP%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%3C%2FTD%3E%3CTD%3EC%3C%2FTD%3E%3CTD%3E%24TI(A2-A2)%2CC%3D5%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EA5%3C%2FTD%3E%3CTD%3EY6DP%3C%2FTD%3E%3CTD%3E%24TI(A7-A8)%2CC%3DR%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%3C%2FTD%3E%3CTD%3EY6DP%3C%2FTD%3E%3CTD%3E%24TI(A3-A4)%2CC%3DC%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EA6%3C%2FTD%3E%3CTD%3EY6DP%3C%2FTD%3E%3CTD%3E%24TI(A9-B9)%2CC%3DC%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%3C%2FTD%3E%3CTD%3EY6DP%3C%2FTD%3E%3CTD%3E%24TI(A5-A6)%2CC%3DY6DP%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EA7%3C%2FTD%3E%3CTD%3ER%3C%2FTD%3E%3CTD%3E%24TI(C0-C4)%2CC%3DY6DP%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%3C%2FTD%3E%3CTD%3ER%3C%2FTD%3E%3CTD%3E%24TI(A7-A8)%2CC%3DR%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EA8%3C%2FTD%3E%3CTD%3ER%3C%2FTD%3E%3CTD%3E%24TI(C5-D0)%2CC%3DJN%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%3C%2FTD%3E%3CTD%3ER%3C%2FTD%3E%3CTD%3E%24TI(A9-B9)%2CC%3DC%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EA9%3C%2FTD%3E%3CTD%3EC%3C%2FTD%3E%3CTD%3E%24TI(D1-D3)%2CC%3DF%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%3C%2FTD%3E%3CTD%3EC%3C%2FTD%3E%3CTD%3E%24TI(C0-C4)%2CC%3DY6DP%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EB0%3C%2FTD%3E%3CTD%3EC%3C%2FTD%3E%3CTD%3E%24TI(D4-D6)%2CC%3D7%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%3C%2FTD%3E%3CTD%3EC%3C%2FTD%3E%3CTD%3E%24TI(C5-D0)%2CC%3DJN%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EB1%3C%2FTD%3E%3CTD%3EC%3C%2FTD%3E%3CTD%3E%24TI(D7-D8)%2CC%3DG%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%3C%2FTD%3E%3CTD%3EC%3C%2FTD%3E%3CTD%3E%24TI(D1-D3)%2CC%3DXZFWI7%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EINIT%3C%2FTD%3E%3CTD%3ECLASS%3C%2FTD%3E%3CTD%3EGROUP2DAY%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EA1%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E%24PI(I1-I5)%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EA2%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E%24TI(A1)%2CC%3D1%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EA3%3C%2FTD%3E%3CTD%3E3%3C%2FTD%3E%3CTD%3E%24TI(A2-A2)%2CC%3D1%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EA4%3C%2FTD%3E%3CTD%3E3%3C%2FTD%3E%3CTD%3E%24TI(A3-A4)%2CC%3D3%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-643217%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Cashflo
Occasional Visitor

I dont know how best to explain my scenario.

Imported data will have 3 types of variables in each cell. A range has to be identified. labels assigned to cells based on that range, populate cells in that range, and change color of cells in that range.
-Type 1 used to identify fill color of cell identified in Type 2($PI=RED,$TI= NO COLOR, $SI=Green)
-Type 2 Range of cells dynamically identified, 2 characters long. The first character will be a letter the second will be number. Letters start with A, numbers start with 1 and end in 0. examples A1-A0, A2-A2, A1-C4. each grouping of data will have different set of ranges 

-Type 3 will be the data for each cell example C=1 C=Y6DP C=RV3S

 

I will have to have Multiple tables on one sheet so i can't use the cells row and column as the Type 2 data  ie.. type 2 data of A3-G4 can't refer to cells A3-G4 on the sheet.

 

Here is what outcome should look like 

INITCLASSGROUP1DAYCLASSGROUP1NITE
A11$TI(A1),C=1            1$PI(I1-I5)             
A21$TI(A2-A2),C=1         5$PI(F7-H6)             
A33$TI(A3-A4),C=3         C$TI(A1),C=1            
A43$TI(A5-A6),C=Y6DP      C$TI(A2-A2),C=5         
A5Y6DP$TI(A7-A8),C=R         Y6DP$TI(A3-A4),C=C         
A6Y6DP$TI(A9-B9),C=C         Y6DP$TI(A5-A6),C=Y6DP      
A7R$TI(C0-C4),C=Y6DP      R$TI(A7-A8),C=R         
A8R$TI(C5-D0),C=JN        R$TI(A9-B9),C=C         
A9C$TI(D1-D3),C=F         C$TI(C0-C4),C=Y6DP      
B0C$TI(D4-D6),C=7         C$TI(C5-D0),C=JN        
B1C$TI(D7-D8),C=G         C$TI(D1-D3),C=XZFWI7    
     
INITCLASSGROUP2DAY  
A11$PI(I1-I5)               
A21$TI(A1),C=1              
A33$TI(A2-A2),C=1           
A43$TI(A3-A4),C=3           
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies