Help me on this formula to shorten

Solved/Closed
mathewmunna
Posts
27
Registration date
Tuesday November 18, 2014
Status
Member
Last seen
May 21, 2015
- Nov 19, 2014 at 06:49 AM
mathewmunna
Posts
27
Registration date
Tuesday November 18, 2014
Status
Member
Last seen
May 21, 2015
- Dec 6, 2014 at 11:42 PM
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

TrowaD
Posts
2880
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 2, 2022
510
Nov 24, 2014 at 11:44 AM
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
Thanks TrowaD.. You made my day..
0
mathewmunna
Posts
27
Registration date
Tuesday November 18, 2014
Status
Member
Last seen
May 21, 2015

Nov 26, 2014 at 01:37 AM
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),"*","")
0
TrowaD
Posts
2880
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 2, 2022
510
Dec 4, 2014 at 10:16 AM
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
0
mathewmunna
Posts
27
Registration date
Tuesday November 18, 2014
Status
Member
Last seen
May 21, 2015
> TrowaD
Posts
2880
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 2, 2022

Dec 6, 2014 at 11:42 PM
Thanks TrowaD..
0