Hello,
I'm been following the instructions on the site entitled "Extract a List Excluding Blank Cells".
As you can see from my attached spread sheet this formula works perfectly in the two examples at the top of the sheet.
I have some names and some blanks in A1-A10 and the formula in A15-A25 copies the names minus the blanks. This also works in column B in the same way.
However, I cannot seem to get it to work further down the sheet. I have some names in B60-B70 and the formula in B71-B80 but it appears not to work in the way it did further up the sheet, does anyone have any ideas why?
This is just a tester sheet as I am trying to use this formula in a rota spread I am working on.
Thanks for any help anyone can offer!
James
There is no attachment
Hello,
Apologies no idea how I missed uploading the attachment.
Hopefully its now showing!
Thanks
Hi James,
The formulas starting in cell B71 are missing absolute referencing and they're not the same. i.e. the ROW formula in the IF formula's value if true argument is ROW(A1:A10) and it should be ROW($A$1:$A$11) because there are 11 rows being referenced. Also the ROW formula in the IF formula's value if false argument should be ROW(B1). Like this:
=IFERROR(INDEX($B$60:$B$70,SMALL(IF(ISTEXT($B$60:$B$70),ROW($A$1:$A$11)), ROW(B1))),"")
Please read this tutorial on how ROW and ROWS is intended for use in these type of formulas.
Mynda
Hi Mynda,
Thanks so much for that, amending the formula as you suggest has solved the issue.
James
