Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Auto Expanding Range for array-entered IF
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Auto Expanding Range for array-entered IF
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" wrote in message ... 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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Auto Expanding Range for array-entered IF
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" wrote: 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" wrote in message ... 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? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Auto Expanding Range for array-entered IF
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 "veggies27" wrote in message ... 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" wrote: 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" wrote in message ... 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
auto-expanding chart- dynamic ranges | Excel Worksheet Functions | |||
How do I enable auto-expanding lists in a protected worksheet? | Excel Discussion (Misc queries) | |||
expanding a range of numbers | Excel Discussion (Misc queries) | |||
Need an Expanding array formula | Excel Worksheet Functions | |||
Auto Expanding Pie Chart | Charts and Charting in Excel |