#N/A when array size mismatch

[Morgan]

Hello,

I have the following formula:

{=IF(ISTEXT(Sheet1!$K$1:$M$1),Sheet1!$K$1:$M$1," ")}

I've actually put this formula in cells Sheet2!K1:Q1, so as to allow
for future expansion of the range on Sheet1. But, on Sheet 2, in cells
N1:Q1, I get #N/A values because the array formula range on Sheet2 does
not match the array argument range on Sheet1.

What I would like to do, if possible, is replace the #N/A values on
Sheet2 with blanks. I've tried using the following:

=IF(ISNA(my formula,"",my formula)

but that doesn't help. I still get #N/A values. Using other IF(IS..
functions does not seem to work, either.

Is there any way to replace the #N/A values when the array size does
not match the array argument range?

Thank you.

 

[Harlan Grove]

I have the following formula:

{=IF(ISTEXT(Sheet1!$K$1:$M$1),Sheet1!$K$1:$M$1," ")}

I've actually put this formula in cells Sheet2!K1:Q1, so as to allow
for future expansion of the range on Sheet1. But, on Sheet 2, in cells
N1:Q1, I get #N/A values because the array formula range on Sheet2 does
not match the array argument range on Sheet1.

...

This is a good example of when *not* to use array formulas.

What do you mean by 'future expansion of the range on Sheet1'? Inserting columns
between columns K and M? Or just entering values in columns N through Q?

Either way, the most robust formula to use in Sheet2!K1 would be

=T(INDIRECT("Sheet1!"&CELL("Address",K1)))

and similarly for the other cells in Sheet2!L1:Q1. If you insert columns in
Sheet1, these formulas in Sheet2 will still pick up the corresponding cell
values from Sheet1. Only changing Sheet1's worksheet name would require formula
maintenance, but that could be overcome using the defined name srcws referring
initially to

=MID(CELL("Filename",Sheet1!A1),FIND("]",CELL("Filename",Sheet1!A1))+1,32)

Then changing the formula for Sheet2!K2 to

=T(INDIRECT("'"&srcws&"'!"&CELL("Address",K1)))

and similarly for Sheet2!L1:Q1.

 

[Morgan]

Harlan,

Thank you. Yes, what I meant by 'future expansion of the range on
Sheet1' was a user inserting a new column between columns K and M. I
created command buttons on Sheet1 for the users to do this so default
values could be populated into the new column using VB.

I tried the formula you mentioned, and it does a great job of getting
rid of the #N/A I was getting before with the array. And of
accomodating the new columns that are inserted on Sheet1.

Thanks again for your help.