Forum Discussion
how to inter formula in a cell has more 8192 characters
hello guys
I am using office 2019
I tried to maintain our work activity progress in the project by making a tracking plane depending on the entry data we have provided it in another sheet table
for an example:
I have a finished work along alignment from
station : 0+300 to 0+500
and from
station : 0+700 to 0+900
i would like highlight the cells under my station's dividing in the sheet
I have used this formula and it was successful:
=IF(OR(AND(A$1>=BEDDING!$A$1,A$1<=BEDDING!$B$1),AND(A$1>=BEDDING!$A$2,A$1<=BEDDING!$B$2),AND(A$1>=BEDDING!$A$3,A$1<=BEDDING!$B$3),AND(A$1>=BEDDING!$A$4,A$1<=BEDDING!$B$4),AND(A$1>=BEDDING!$A$5,A$1<=BEDDING!$B$5),AND(A$1>=BEDDING!$A$6,A$1<=BEDDING!$B$6),AND(A$1>=BEDDING!$A$7,A$1<=BEDDING!$B$7),AND(A$1>=BEDDING!$A$8,A$1<=BEDDING!$B$8),AND(A$1>=BEDDING!$A$9,A$1<=BEDDING!$B$9),AND(A$1>=BEDDING!$A$10,A$1<=BEDDING!$B$10),AND(A$1>=BEDDING!$A$11,A$1<=BEDDING!$B$11),AND(A$1>=BEDDING!$A$12,A$1<=BEDDING!$B$12),AND(A$1>=BEDDING!$A$13,A$1<=BEDDING!$B$13),AND(A$1>=BEDDING!$A$14,A$1<=BEDDING!$B$14),AND(A$1>=BEDDING!$A$15,A$1<=BEDDING!$B$15),AND(A$1>=BEDDING!$A$16,A$1<=BEDDING!$B$16),AND(A$1>=BEDDING!$A$17,A$1<=BEDDING!$B$17),AND(A$1>=BEDDING!$A$18,A$1<=BEDDING!$B$18),AND(A$1>=BEDDING!$A$19,A$1<=BEDDING!$B$19),AND(A$1>=BEDDING!$A$20,A$1<=BEDDING!$B$20),AND(A$1>=BEDDING!$A$21,A$1<=BEDDING!$B$21),AND(A$1>=BEDDING!$A$22,A$1<=BEDDING!$B$22),AND(A$1>=BEDDING!$A$23,A$1<=BEDDING!$B$23),AND(A$1>=BEDDING!$A$24,A$1<=BEDDING!$B$24),AND(A$1>=BEDDING!$A$25,A$1<=BEDDING!$B$25),AND(A$1>=BEDDING!$A$26,A$1<=BEDDING!$B$26),AND(A$1>=BEDDING!$A$27,A$1<=BEDDING!$B$27),AND(A$1>=BEDDING!$A$28,A$1<=BEDDING!$B$28),AND(A$1>=BEDDING!$A$29,A$1<=BEDDING!$B$29),AND(A$1>=BEDDING!$A$30,A$1<=BEDDING!$B$30),AND(A$1>=BEDDING!$A$31,A$1<=BEDDING!$B$31),AND(A$1>=BEDDING!$A$32,A$1<=BEDDING!$B$32),AND(A$1>=BEDDING!$A$33,A$1<=BEDDING!$B$33),AND(A$1>=BEDDING!$A$34,A$1<=BEDDING!$B$34),AND(A$1>=BEDDING!$A$35,A$1<=BEDDING!$B$35),AND(A$1>=BEDDING!$A$36,A$1<=BEDDING!$B$36),AND(A$1>=BEDDING!$A$37,A$1<=BEDDING!$B$37),AND(A$1>=BEDDING!$A$38,A$1<=BEDDING!$B$38),AND(A$1>=BEDDING!$A$39,A$1<=BEDDING!$B$39),AND(A$1>=BEDDING!$A$40,A$1<=BEDDING!$B$40),AND(A$1>=BEDDING!$A$41,A$1<=BEDDING!$B$41),AND(A$1>=BEDDING!$A$42,A$1<=BEDDING!$B$42),AND(A$1>=BEDDING!$A$43,A$1<=BEDDING!$B$43),AND(A$1>=BEDDING!$A$44,A$1<=BEDDING!$B$44),AND(A$1>=BEDDING!$A$45,A$1<=BEDDING!$B$45),AND(A$1>=BEDDING!$A$46,A$1<=BEDDING!$B$46),AND(A$1>=BEDDING!$A$47,A$1<=BEDDING!$B$47),AND(A$1>=BEDDING!$A$48,A$1<=BEDDING!$B$48),AND(A$1>=BEDDING!$A$49,A$1<=BEDDING!$B$49),AND(A$1>=BEDDING!$A$50,A$1<=BEDDING!$B$50),AND(A$1>=BEDDING!$A$51,A$1<=BEDDING!$B$51),AND(A$1>=BEDDING!$A$52,A$1<=BEDDING!$B$52),AND(A$1>=BEDDING!$A$53,A$1<=BEDDING!$B$53),AND(A$1>=BEDDING!$A$54,A$1<=BEDDING!$B$54),AND(A$1>=BEDDING!$A$55,A$1<=BEDDING!$B$55),AND(A$1>=BEDDING!$A$56,A$1<=BEDDING!$B$56),AND(A$1>=BEDDING!$A$57,A$1<=BEDDING!$B$57),AND(A$1>=BEDDING!$A$58,A$1<=BEDDING!$B$58),AND(A$1>=BEDDING!$A$59,A$1<=BEDDING!$B$59),AND(A$1>=BEDDING!$A$60,A$1<=BEDDING!$B$60),AND(A$1>=BEDDING!$A$61,A$1<=BEDDING!$B$61),AND(A$1>=BEDDING!$A$62,A$1<=BEDDING!$B$62),AND(A$1>=BEDDING!$A$63,A$1<=BEDDING!$B$63),AND(A$1>=BEDDING!$A$64,A$1<=BEDDING!$B$64),AND(A$1>=BEDDING!$A$65,A$1<=BEDDING!$B$65),AND(A$1>=BEDDING!$A$66,A$1<=BEDDING!$B$66),AND(A$1>=BEDDING!$A$67,A$1<=BEDDING!$B$67),AND(A$1>=BEDDING!$A$68,A$1<=BEDDING!$B$68),AND(A$1>=BEDDING!$A$69,A$1<=BEDDING!$B$69),AND(A$1>=BEDDING!$A$70,A$1<=BEDDING!$B$70),AND(A$1>=BEDDING!$A$71,A$1<=BEDDING!$B$71),AND(A$1>=BEDDING!$A$72,A$1<=BEDDING!$B$72),AND(A$1>=BEDDING!$A$73,A$1<=BEDDING!$B$73),AND(A$1>=BEDDING!$A$74,A$1<=BEDDING!$B$74),AND(A$1>=BEDDING!$A$75,A$1<=BEDDING!$B$75),AND(A$1>=BEDDING!$A$76,A$1<=BEDDING!$B$76),AND(A$1>=BEDDING!$A$77,A$1<=BEDDING!$B$77),AND(A$1>=BEDDING!$A$78,A$1<=BEDDING!$B$78),AND(A$1>=BEDDING!$A$79,A$1<=BEDDING!$B$79),AND(A$1>=BEDDING!$A$80,A$1<=BEDDING!$B$80),AND(A$1>=BEDDING!$A$81,A$1<=BEDDING!$B$81),AND(A$1>=BEDDING!$A$82,A$1<=BEDDING!$B$82),AND(A$1>=BEDDING!$A$83,A$1<=BEDDING!$B$83),AND(A$1>=BEDDING!$A$84,A$1<=BEDDING!$B$84),AND(A$1>=BEDDING!$A$85,A$1<=BEDDING!$B$85),AND(A$1>=BEDDING!$A$86,A$1<=BEDDING!$B$86),AND(A$1>=BEDDING!$A$87,A$1<=BEDDING!$B$87),AND(A$1>=BEDDING!$A$88,A$1<=BEDDING!$B$88),AND(A$1>=BEDDING!$A$89,A$1<=BEDDING!$B$89),AND(A$1>=BEDDING!$A$90,A$1<=BEDDING!$B$90),AND(A$1>=BEDDING!$A$91,A$1<=BEDDING!$B$91),AND(A$1>=BEDDING!$A$92,A$1<=BEDDING!$B$92),AND(A$1>=BEDDING!$A$93,A$1<=BEDDING!$B$93),AND(A$1>=BEDDING!$A$94,A$1<=BEDDING!$B$94),AND(A$1>=BEDDING!$A$95,A$1<=BEDDING!$B$95),AND(A$1>=BEDDING!$A$96,A$1<=BEDDING!$B$96),AND(A$1>=BEDDING!$A$97,A$1<=BEDDING!$B$97),AND(A$1>=BEDDING!$A$98,A$1<=BEDDING!$B$98),AND(A$1>=BEDDING!$A$99,A$1<=BEDDING!$B$99),AND(A$1>=BEDDING!$A$100,A$1<=BEDDING!$B$100)),1,0)
but I can't enter character more than 8192
so is there any solution for it or other way to solve it
best regards
Here are two different questions
1) how to highlight some cells
2) how to apply the formula in sample
First question - didn't catch the logic, not sure
Second question - use alternative formula which returns desired result. It could be
=--(SUMPRODUCT( (A$1>=Bedding!$A$1:A$100)*(A$1<=Bedding!$B$1:B$100))>0)
9 Replies
- SergeiBaklanDiamond Contributor
Here are two different questions
1) how to highlight some cells
2) how to apply the formula in sample
First question - didn't catch the logic, not sure
Second question - use alternative formula which returns desired result. It could be
=--(SUMPRODUCT( (A$1>=Bedding!$A$1:A$100)*(A$1<=Bedding!$B$1:B$100))>0)- alexanderjamlCopper Contributor
thank you i appreciate your help
It works wellSergeiBaklan
- SergeiBaklanDiamond Contributor
alexanderjaml , glad to know it helped
- Riny_van_EekelenPlatinum Contributor
alexanderjaml Can you disclose what you have in the BEDDING sheet and what output you expect in the progress chart? I'm pretty sure you don't need such a monstrous formula for whatever you are trying to do.
- alexanderjamlCopper Contributor
yeah i find it
thank u so much Riny_van_Eekelen
=--(SUMPRODUCT( (A$1>=Bedding!$A$1:A$100)*(A$1<=Bedding!$B$1:B$100))>0)