remove blank cells from list ?

[Morgan]

Hello,

On Sheet1, I have a matrix of data (D4:R23, with many cells blank) tha
is updated by users, and that I’m gradually reformatting into fairl
straightforward columns. Then on Sheet 2, I transpose the data fro
Sheet1. Since additional rows and columns could be later inserted b
users on Sheet1, my transpose array on Sheet2 is defined to includ
more columns/rows than exist on Sheet1, which results in #N/A value
showing up on Sheet2. My goal with Sheet3 is to have only the tex
values that were transposed on Sheet2 – no blanks, zeros, or #N/
values. The step-by-step approach may be making this more complicate
than it needs to be, but have not found any other way around it.

The only way I’ve found to remove blank spaces was by going column b
column using the following formula:

{=IF(ROW()-ROW(NoBlanksRange)+1>ROWS(BlanksRange)-COUNTBLANK(BlanksRange),"",INDIRECT(ADDRESS(SMALL((IF(BlanksRange<>"",ROW(BlanksRange),ROW()+ROWS(BlanksRange))),ROW()-ROW(NoBlanksRange)+1),COLUMN(BlanksRange),4)))}

But because my data includes formulas, I get zeros for any blank cells
In order to remove any zeros or array error codes, I found th
following seems to work:

{=IF(ISTEXT(IF(ROW()-ROW(NoBlanksRange)+1>ROWS(BlanksRange)-COUNTBLANK(BlanksRange),"",INDIRECT(ADDRESS(SMALL((IF(ISTEXT(BlanksRange),ROW(BlanksRange),ROW()+ROWS(BlanksRange))),ROW()-ROW(NoBlanksRange)+1),COLUMN(BlanksRange),4)))),IF(ROW()-ROW(NoBlanksRange)+1>ROWS(BlanksRange)-COUNTBLANK(BlanksRange),"",INDIRECT(ADDRESS(SMALL((IF(ISTEXT(BlanksRange),ROW(BlanksRange),ROW()+ROWS(BlanksRange))),ROW()-ROW(NoBlanksRange)+1),COLUMN(BlanksRange),4))),
")}

However, in my initial testing, I was working with ranges that were o
the same worksheet, but different columns (i.e. $A$3:$A$27 and
$F$3:$F$27).

My problems started when I tried to use the formula for data o
different worksheets. In this case, ‘BlanksRange’ refers t
‘Sheet2’!$A$3:$A$27, and ‘NoBlanksRange’ refers t
‘Sheet3’!$A$3:$A$27.

I now get circular reference errors. Is there a way to link data t
another worksheet, and along the way remove anything other than text?

Thank you