dynamic formula that references the column row within the formula

[kiwikiki718]

Hi I was wondering there was a formula that I can use where one of the references could be dynamic based upon the column the formula is in.


for example I have the below formula in column B

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

Instead of referencing B1 I want to know I can replace B1 to reflect whatever column the formula is in. so if the above formula was in Column C then "B1" would show C1 within the formula.


thanks in advance

 

[Fluff]

How about like

=LET(a,TEXTBEFORE(INDEX(A1:ZZ1,,COLUMN()),"_"),IF(MONDAY!J3=a,MONDAY!K3,IF(MONDAY!J4=a,MONDAY!K4)))

 

[kiwikiki718]

How about like

=LET(a,TEXTBEFORE(INDEX(A1:ZZ1,,COLUMN()),"_"),IF(MONDAY!J3=a,MONDAY!K3,IF(MONDAY!J4=a,MONDAY!K4)))

Thank you I made the updates to the formula but now the formula shows a text in excel.

=LET(a,TEXTBEFORE(INDEX(A1:ZZ1,,COLUMN()),"_"), IF(MONDAY!J3=a,MONDAY!K3,IF(MONDAY!J4=a,MONDAY!K4,IF(MONDAY!J5=a,MONDAY!K5,IF(MONDAY!J6=a,MONDAY!K6,IF(MONDAY!J7=a,MONDAY!K7,IF(MONDAY!J8=a,MONDAY!K8,IF(MONDAY!J9=a,MONDAY!K9,IF(MONDAY!J10=a,MONDAY!K10,IF(MONDAY!J11=a,MONDAY!K11,IF(MONDAY!J12=a,MONDAY!K12,""))))))))))

 

[Fluff]

You're missing a final closing bracket.

 

[kiwikiki718]

You're missing a final closing bracket.

Thank you! It worked! only issue is that the values that are supposed to be blank are showing false

 

[kiwikiki718]

Looks like when I entered the formula, the excel auto correct option popped up and I selected ok and it reverted back referencing the actual cell.

 

[Fluff]

Glad to help & thanks for the feedback.