Home > Back-end >  Return value to a text if all cells are set to FALSE using =Index
Return value to a text if all cells are set to FALSE using =Index

Time:05-25

i am trying to write a formula which when multiple cells are all "False" the cell will change to "Nothing" else if one of them is "True" it will change to that Cell tab name.

the thing is it worked fine regarding the 2nd part

[Gif Picture 1

and i used this formula

=INDEX($S$3:$V$3,SUMPRODUCT(MAX(($S$4:$V$4=O16)*(COLUMN($S$4:$V$4))))-COLUMN($S$3) 1)

but the problem is when all are False i get #VALUE! so i found this code here on Stack

=IF(ISBLANK(<range>),"-",<your code>)

and used it on my Formula as follows

=IF(ISBLANK(W4),"-"=INDEX($S$3:$V$3,SUMPRODUCT(MAX(($S$4:$V$4=O16)*(COLUMN($S$4:$V$4))))-COLUMN($S$3) 1))

but i got False all the times without change

Gif Picture 2

CodePudding user response:

Let me give you a piece of advise: the =OR(...) function turns a bunch of FALSEs into one FALSE, but as soon as you have one TRUE, the result becomes TRUE:

enter image description here

CodePudding user response:

i manged to fix it as Dominique recommended and i added 2 new cloumns one of them which is the old fourmla

=INDEX($S$3:$V$3,SUMPRODUCT(MAX(($S$4:$V$4=O16)*(COLUMN($S$4:$V$4))))-COLUMN($S$3) 1)

the 2nd one is

=IF(W4="Nothing","FALSE","TRUE")

and edited the Status tab to be

=IF(ISNUMBER(SEARCH(X4,O17)),W4,Y4)

and at O17 a text wrriten "FALSE". so if checked any box the tab name will be wriiten in Status else if none checked it will be wrriten Nothing.

Gif example:

Final

  • Related