Loop formula VBA Code

[kiwikiki718]

I have several columns (M&N,O&P,Q&R,S&T etc.) where I have a formula entered to return a value based upon if a nested if statement is true. 2 cells are very similar with the formula for ex. M2 & N2 are very similar except the column to return if the statement is true is different. example for column M the return value is being pulled from column K and for column N, the return values is being pulled from column L.

Formula examples:

For column M text Is being compared to the text in cell M1 see below example

cell M2 Formula
=IF(MONDAY!J3=(TEXTBEFORE(M1,"_")),MONDAY!K3,IF(MONDAY!J4=(TEXTBEFORE(M1,"_")),MONDAY!K4,IF(MONDAY!J5=(TEXTBEFORE(M1,"_")),MONDAY!K5,IF(MONDAY!J6=(TEXTBEFORE(M1,"_")),MONDAY!K6,IF(MONDAY!J7=(TEXTBEFORE(M1,"_")),MONDAY!K7,IF(MONDAY!J8=(TEXTBEFORE(M1,"_")),MONDAY!K8,IF(MONDAY!J9=(TEXTBEFORE(M1,"_")),MONDAY!K9,IF(MONDAY!J10=(TEXTBEFORE(M1,"_")),MONDAY!K10,IF(MONDAY!J11=(TEXTBEFORE(M1,"_")),MONDAY!K11,IF(MONDAY!J12=(TEXTBEFORE(M1,"_")),MONDAY!K12,""))))))))))

Cell M3 Formula I have
=IF(MONDAY!J13=(TEXTBEFORE(M1,"_")),MONDAY!K13,IF(MONDAY!J14=(TEXTBEFORE(M1,"_")),MONDAY!K14,IF(MONDAY!J15=(TEXTBEFORE(M1,"_")),MONDAY!K15,IF(MONDAY!J16=(TEXTBEFORE(M1,"_")),MONDAY!K16,IF(MONDAY!J17=(TEXTBEFORE(M1,"_")),MONDAY!K17,IF(MONDAY!J18=(TEXTBEFORE(M1,"_")),MONDAY!K18,IF(MONDAY!J19=(TEXTBEFORE(M1,"_")),MONDAY!K19,IF(MONDAY!J20=(TEXTBEFORE(M1,"_")),MONDAY!K20,IF(MONDAY!J21=(TEXTBEFORE(M1,"_")),MONDAY!K21,IF(MONDAY!J22=(TEXTBEFORE(M1,"_")),MONDAY!K22,""))))))))))

cell M4 formula I have
=IF(MONDAY!J23=(TEXTBEFORE(M1,"_")),MONDAY!K23,IF(MONDAY!J24=(TEXTBEFORE(M1,"_")),MONDAY!K24,IF(MONDAY!J25=(TEXTBEFORE(M1,"_")),MONDAY!K25,IF(MONDAY!J26=(TEXTBEFORE(M1,"_")),MONDAY!K26,IF(MONDAY!J27=(TEXTBEFORE(M1,"_")),MONDAY!K27,IF(MONDAY!J28=(TEXTBEFORE(M1,"_")),MONDAY!K28,IF(MONDAY!J29=(TEXTBEFORE(M1,"_")),MONDAY!K29,IF(MONDAY!J30=(TEXTBEFORE(M1,"_")),MONDAY!K30,IF(MONDAY!J31=(TEXTBEFORE(M1,"_")),MONDAY!K31,IF(MONDAY!J32=(TEXTBEFORE(M1,"_")),MONDAY!K32,""))))))))))

This continues all the way up to cell M9 (with the cells increasing by 10 each time)

similar to columns O,Q,S etc. only difference is it is comparing the text to the 1st cell in that column, example for column O
for cell O2 I have
=IF(MONDAY!J3=(TEXTBEFORE(O1,"_")),MONDAY!K3,IF(MONDAY!J4=(TEXTBEFORE(O1,"_")),MONDAY!K4,IF(MONDAY!J5=(TEXTBEFORE(O1,"_")),MONDAY!K5,IF(MONDAY!J6=(TEXTBEFORE(O1,"_")),MONDAY!K6,IF(MONDAY!J7=(TEXTBEFORE(O1,"_")),MONDAY!K7,IF(MONDAY!J8=(TEXTBEFORE(O1,"_")),MONDAY!K8,IF(MONDAY!J9=(TEXTBEFORE(O1,"_")),MONDAY!K9,IF(MONDAY!J10=(TEXTBEFORE(O1,"_")),MONDAY!K10,IF(MONDAY!J11=(TEXTBEFORE(O1,"_")),MONDAY!K11,IF(MONDAY!J12=(TEXTBEFORE(O1,"_")),MONDAY!K12,""))))))))))


For column N text Is being compared to the text in cell N1 see below example (only thing different is the column for the return value if true is Column L)

Cell N2 Formula
=IF(MONDAY!J3=(TEXTBEFORE(N1,"_")),MONDAY!L3,IF(MONDAY!J4=(TEXTBEFORE(N1,"_")),MONDAY!L4,IF(MONDAY!J5=(TEXTBEFORE(N1,"_")),MONDAY!L5,IF(MONDAY!J6=(TEXTBEFORE(N1,"_")),MONDAY!L6,IF(MONDAY!J7=(TEXTBEFORE(N1,"_")),MONDAY!L7,IF(MONDAY!J8=(TEXTBEFORE(N1,"_")),MONDAY!L8,IF(MONDAY!J9=(TEXTBEFORE(N1,"_")),MONDAY!K=L9,IF(MONDAY!J10=(TEXTBEFORE(N1,"_")),MONDAY!L10,IF(MONDAY!J11=(TEXTBEFORE(N1,"_")),MONDAY!L11,IF(MONDAY!J12=(TEXTBEFORE(N1,"_")),MONDAY!L12,""))))))))))

Cell N3 Formula
=IF(MONDAY!J13=(TEXTBEFORE(N1,"_")),MONDAY!L13,IF(MONDAY!J14=(TEXTBEFORE(N1,"_")),MONDAY!L14,IF(MONDAY!J15=(TEXTBEFORE(N1,"_")),MONDAY!L15,IF(MONDAY!J16=(TEXTBEFORE(N1,"_")),MONDAY!L16,IF(MONDAY!J17=(TEXTBEFORE(N1,"_")),MONDAY!L17,IF(MONDAY!J18=(TEXTBEFORE(N1,"_")),MONDAY!L18,IF(MONDAY!J19=(TEXTBEFORE(N1,"_")),MONDAY!L19,IF(MONDAY!J20=(TEXTBEFORE(N1,"_")),MONDAY!L20,IF(MONDAY!J21=(TEXTBEFORE(N1,"_")),MONDAY!L21,IF(MONDAY!J22=(TEXTBEFORE(N1,"_")),MONDAY!L22,""))))))))))

Cell N4 Formula
=IF(MONDAY!J23=(TEXTBEFORE(N1,"_")),MONDAY!L23,IF(MONDAY!J24=(TEXTBEFORE(N1,"_")),MONDAY!L24,IF(MONDAY!J25=(TEXTBEFORE(N1,"_")),MONDAY!L25,IF(MONDAY!J26=(TEXTBEFORE(N1,"_")),MONDAY!L26,IF(MONDAY!J27=(TEXTBEFORE(N1,"_")),MONDAY!L27,IF(MONDAY!J28=(TEXTBEFORE(N1,"_")),MONDAY!L28,IF(MONDAY!J29=(TEXTBEFORE(N1,"_")),MONDAY!L29,IF(MONDAY!J30=(TEXTBEFORE(N1,"_")),MONDAY!L30,IF(MONDAY!J31=(TEXTBEFORE(N1,"_")),MONDAY!L31,IF(MONDAY!J32=(TEXTBEFORE(N1,"_")),MONDAY!L32,""))))))))))

this continues all the way up to cell M9 (with the cells increasing by 10 each time)


similar to columns P,R,T etc. only difference is it is comparing the text to the 1st cell in that column, ex for column P
for cell P2 I have
=IF(MONDAY!J3=(TEXTBEFORE(P1,"_")),MONDAY!L3,IF(MONDAY!J4=(TEXTBEFORE(P1,"_")),MONDAY!L4,IF(MONDAY!J5=(TEXTBEFORE(P1,"_")),MONDAY!L5,IF(MONDAY!J6=(TEXTBEFORE(P1,"_")),MONDAY!L6,IF(MONDAY!J7=(TEXTBEFORE(P1,"_")),MONDAY!L7,IF(MONDAY!J8=(TEXTBEFORE(P1,"_")),MONDAY!L8,IF(MONDAY!J9=(TEXTBEFORE(P1,"_")),MONDAY!K=L9,IF(MONDAY!J10=(TEXTBEFORE(P1,"_")),MONDAY!L10,IF(MONDAY!J11=(TEXTBEFORE(P1,"_")),MONDAY!L11,IF(MONDAY!J12=(TEXTBEFORE(P1,"_")),MONDAY!L12,""))))))))))


is there some sort of Vba code that can automatically execute this without me having to manually enter the formula in each cell?

Any help would be appreciated.

 

[kiwikiki718]

also I forget to mention column J is being pulled from another worksheet called (MONDAY). everything this else is referencing the active worksheet called (UPDATES)