Jun 04 2022 02:42 AM
Hello,
I've column that's include a values and blanks so i just need to move only values without taking the blanks with the formula
what i tried is :
used =IF(Main!O5,Main!J5,"") so the formula find true in the Main sheet and then return the value of column " J "
what i've this :
TRUE |
TRUE |
TRUE |
TRUE |
FALSE |
TRUE |
TRUE |
TRUE |
FALSE |
FALSE |
what i get as a result is :
Mini,4x12 |
Armored |
#VALUE! |
#VALUE! |
#VALUE! |
#VALUE! |
#VALUE! |
#VALUE! |
#VALUE! |
#VALUE! |
#VALUE! |
HDPE 2 |
Mini12 |
Mini, 8x12 |
MiniXt 12x12F |
Mini-DQ(ZN)2Y, 18x12 |
so i don't need the result to be blanks and #VALUE! i need only getting the result of TRUE
Jun 04 2022 07:07 AM
Jun 04 2022 09:59 AM
If it is simply a question of filtering out blanks from a list then, using 365, the following would work
= FILTER(list, list<>"")
In legacy Excel it is distinctly more messy
= INDEX(list,SMALL(IF(LEN(list)>0,seq),seq))
committed with CSE.
Jun 04 2022 12:17 PM
I want to do it automatically because i'm dealing with large data so i can't select and delete blanks every time i entered the data
Jun 04 2022 12:19 PM
Jun 04 2022 12:53 PM
Jun 05 2022 01:11 AM
'list' is a defined name that I have applied to whatever range of values from which you wish to remove the blanks. If the quantities are meaningful to you, then it would be reasonable to apply a more specific name.
[Having described A1 type references as an abomination that should never entered any computational environment anywhere (thus placing me at odds with virtually every spreadsheet user) I am not the right person to consult on the mysteries of the semi-anchored, directly-referenced range.]