any way to change array size?

[Morgan]

On Sheet1, I have values in A4:A20 similar to below:

profile1
profile2
profile3
profile4
profile5
profile6

On Sheet 2, I use the following formula to link the Sheet1 values to
Sheet2!D1:T1

{=TRANSPOSE(IF(ISTEXT(Sheet1!$A$4:$A$20),Sheet1!$A$4:$A$20," "))}

The problem I am having is that the range A4:A20 may expand beyond the
initial size once users start recording data. When that happens,
Sheet2 will no longer show all values because the array is defined for
only 17 values.

I can always define the array on Sheet2 with extra columns (say... out
to GG1 or something), but the extra columns will have #NA in them,
which will show up on any print outs.

My first question is... is there a way to write the formula on Sheet2
to expand the array when additional values are entered on Sheet1?

If not, and I just define my array on Sheet2 to have extra columns to
begin with, is there a way to space/blank out the #NA values in unused
columns so that those columns/values won't print?

Any ideas would be appreciated.
Thank you.

 

[Myrna Larson]

Have you tried your proposed formula? ISTEXT will fail if the cell contains an error value like
#NA()! so you should be able to just expand the range to the largest possible size.

 

[Morgan]

Myrna,

Yes, I've tried the formula mentioned in my initial post. But,
the ISTEXT is used against the cells on Sheet1, and the #NA shows up in
the unused columns on Sheet2.
I have also tried adding...
IF(ERROR.TYPE(Sheet2!$D$1:$T$1)=7, " "," "
to my formula, but in this case, I get a circular reference error. So,
that hasn't worked either.
Thanks.

Morgan