Help me on this formula to shorten [Solved/Closed]

Report
Posts
27
Registration date
Tuesday November 18, 2014
Status
Member
Last seen
May 21, 2015
-
Posts
27
Registration date
Tuesday November 18, 2014
Status
Member
Last seen
May 21, 2015
-
Hello,

i have 2 formulas tick mark automatically on worksheet.. please help me if further it can be shortened.. i want to know if J1 K1 L1 can shown as J1:L1 using any condition.. please help me out to sort out this..

=IF($J$1=ROW(A1),"*",(IF($K$1=ROW(A1),"*",(IF($L$1=ROW(A1),"*","")))))


=IF(OR($J$2=ROW(B1),$K$2=ROW(B1),$L$2=ROW(B1)),"*","")

1 reply

Posts
2656
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 17, 2020
440
Hi Mathew,

Try this array formula, which needs to be confirmed by hitting Ctrl+Shift+Enter:

=IF(OR($J$1:$L$1=ROW(A1)=TRUE),"*","")

If done correctly the formula will be enclosed by curly brackets.

Best regards,
Trowa
Monday, Tuesday and Thursday are usually the days I'll respond. Bear this in mind when awaiting a reply.
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2905 users have said thank you to us this month

Thanks TrowaD.. You made my day..
Posts
27
Registration date
Tuesday November 18, 2014
Status
Member
Last seen
May 21, 2015

Dear Trowa is there any option to increment the value of $J$1:$L$1
when i drag the formula to other cell $J$2:$L$2.....


A1 =IF(OR($J$1:$L$1=ROW(A1)=TRUE),"*","")
B1 =IF(OR($J$2:$L$2=ROW(B1)=TRUE),"*","")
C1 =IF(OR($J$2:$L$2=ROW(C1)=TRUE),"*","")
Posts
2656
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 17, 2020
440
Hi Mathew,

Sorry for my late response, have been ill the past week.

A dollar symbol will fix a column or row, so remove the dollar sign in front of the row number:
=IF(OR($J1:$L1=ROW(A1)=TRUE),"*","")

Best regards,
Trowa
Posts
27
Registration date
Tuesday November 18, 2014
Status
Member
Last seen
May 21, 2015
>
Posts
2656
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 17, 2020

Thanks TrowaD..