Auto Expanding Range for array-entered IF

[veggies27]

I have an array-entered IF formula in Sheet 1 that looks up data in a range
of rows in sheet2 column a, and returns matching data from Sheet 2 column b
to Sheet 1.

I'd like to be able to insert rows in the middle of my range in Sheet 2 and
have the array-entered IF formula in sheet 1 somehow adjust accrodingly.

Does anyone know if this is possible?

 

[Bernie Deitrick]

Excel will automatically update any cell references that are expanded by the
insertion of a row(s), column(s), or cell(s), as long as the insertion is
done "within" the range. This includes references in array-entered
formulas.

HTH,
Bernie
MS Excel MVP

 

[veggies27]

Ok. Thanks. Now on to maybe the more difficult question. I have array-entered
if formulas in a summary sheet that pull from 16 other sheets. In the summary
sheet, rows 1-10 for instance pull from sheet1 rows 1-10. Summary sheet rows
11-20 pull from sheet 2 rows 1-10. When I add rows into the middle of the
range in sheet1 (say row 6), it expands my array-entered IF formula in each
cell, but it basically loses the last row in the summary sheet (if I add 1
row to the range in sheet1). If I enter the array-entered IF fomula to
include only 10 rows, and my range on the data entry sheet now has 11, the
formula changes, but how can I get it to add the 11th row to the summary
sheet? Is there any way to automatically insert rows, and keep them in the
same arry-entered formula as above?

 

[Bernie Deitrick]

veggies,

When you set up your sheets, color code rows 1-11 (even though you only have data in rows 1-10). In
your formulas, reference rows 1 - 11, not 1-10. Then, when you want to add a row to the bottom of,
say, sheet1, select row 11 and insert a row. The end result will be that - you have a new colored
row (Row 11), and there will still be a colored blank row at the bottom (now row 12), and your
formulas will update to reference rows 1-12.

Another option is to used named ranges that are automatically expanded. Another option is to use
Tools / Options... Edit tab, and check "Expand data range formats and formulas" It doesn't always
work as expected, so I wouldn't count on it

HTH,
Bernie
MS Excel MVP