Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Fill array with fn variables that vary across and down?
I have an RxC array which contains formulas in each cell. The formula has a
variable that varies vertically & horizontally, and a variable that varies horizontally by row: E.g: =E2-$O$2, =F2-$O$2, ... =E3-$O$3, =F3-$O$3, ... .... I would like to find a better way to populate the array as I add rows. I would greatly appreciate a shortcut or macro or ?? that will help me do this. The first variable works properly when I fill right and down, but the second one ($O$2) varies by row, but then stays the same across the width of the array. Thanks much! dmn |
#2
|
|||
|
|||
"Llurker" wrote in message . .. I have an RxC array which contains formulas in each cell. The formula has a variable that varies vertically & horizontally, and a variable that varies horizontally by row: E.g: =E2-$O$2, =F2-$O$2, ... =E3-$O$3, =F3-$O$3, ... ... I would like to find a better way to populate the array as I add rows. I would greatly appreciate a shortcut or macro or ?? that will help me do this. The first variable works properly when I fill right and down, but the second one ($O$2) varies by row, but then stays the same across the width of the array. Thanks much! If I understand correctly =E2-$O2 should help. Ian |
#3
|
|||
|
|||
Hi, Ian--
Thanks, but that's not quite the right thing. Sorry if I mis-explained. $O2 will vary the 2 across and down as I fill the array, or add a row. What I'm looking for is something that will vary it down, but keep it constant in each row: row 1 (4 cols): $O$2, $O$2, $O$2, $O$2 row 2 (4 cols): $O$3, $O$3, $O$3, $O$3 row 3 (4 cols): $O$4, $O$4, $O$4, $O$4 etc. I tried doing something like =D2-$ORow(), which I hoped would resolve to D2-$O3 (for example). Is there a way to create a cell reference like this? Thank you for your help! dmn "IC" wrote in message ... "Llurker" wrote in message . .. I have an RxC array which contains formulas in each cell. The formula has a variable that varies vertically & horizontally, and a variable that varies horizontally by row: E.g: =E2-$O$2, =F2-$O$2, ... =E3-$O$3, =F3-$O$3, ... ... I would like to find a better way to populate the array as I add rows. I would greatly appreciate a shortcut or macro or ?? that will help me do this. The first variable works properly when I fill right and down, but the second one ($O$2) varies by row, but then stays the same across the width of the array. Thanks much! If I understand correctly =E2-$O2 should help. Ian |
#4
|
|||
|
|||
I figured out that I can use the offset function to do what I need. That
allows me to vary the row and keep the column offset fixed for as wide an array as I need. =E2-offset($O2,0,5) this formula is the same in all fields of the array; it varies by row but not by column. "Llurker" wrote in message . .. I have an RxC array which contains formulas in each cell. The formula has a variable that varies vertically & horizontally, and a variable that varies horizontally by row: E.g: =E2-$O$2, =F2-$O$2, ... =E3-$O$3, =F3-$O$3, ... ... I would like to find a better way to populate the array as I add rows. I would greatly appreciate a shortcut or macro or ?? that will help me do this. The first variable works properly when I fill right and down, but the second one ($O$2) varies by row, but then stays the same across the width of the array. Thanks much! dmn |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
fill down array to last available row | Excel Worksheet Functions | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) | |||
How to use array formula for three variables? | Excel Discussion (Misc queries) | |||
Formula to list unique values | Excel Worksheet Functions | |||
VBA Import of text file & Array parsing of that data | Excel Discussion (Misc queries) |