# 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 response

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
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.
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),"*","")
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
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
mathewmunna Posts 27 Registration date Tuesday November 18, 2014 Status Member Last seen May 21, 2015 > TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022
Dec 6, 2014 at 11:42 PM