| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Drag-filling a formula Array
I want to create a summary of data and have a solid formula (using such
formula's as match, lookup, and vlookup). Unfortunately, when I try to drag these formulas, the arrays that they correspond to are not the ones I desire. For example, my original array is A10:A20 and want the next one to be A20:A30, but when I start dragging it goes to A11:A21. Even with multiple cells manually edited then dragged, it still doesn't want to do it in the format I want. Anyone?? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Drag-filling a formula Array
In row1 use
INDIRECT("A"&(ROW()*10)&":A"&(ROW()+1)*10) whereever you had A10:A20 and then copy the formula down... Do note that A10:A20, A20:A30 are overlapping or did you mean A11:A20, A21:A30,...? ------------------------------------- Pl. click ''''Yes'''' if this was helpful... "Jacob C." wrote: I want to create a summary of data and have a solid formula (using such formula's as match, lookup, and vlookup). Unfortunately, when I try to drag these formulas, the arrays that they correspond to are not the ones I desire. For example, my original array is A10:A20 and want the next one to be A20:A30, but when I start dragging it goes to A11:A21. Even with multiple cells manually edited then dragged, it still doesn't want to do it in the format I want. Anyone?? |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Drag-filling a formula Array
I didn't mean to over lap. My actual cells are E3:E12 then E13:E22, E23:E32
etc. etc. The formula you gave me didn't seem to help, though I don't really know how it works. Maybe with the specific cell numbers it will be more clear, but remember I am trying to put it into a lookup array. "Sheeloo" wrote: In row1 use INDIRECT("A"&(ROW()*10)&":A"&(ROW()+1)*10) whereever you had A10:A20 and then copy the formula down... Do note that A10:A20, A20:A30 are overlapping or did you mean A11:A20, A21:A30,...? ------------------------------------- Pl. click ''''Yes'''' if this was helpful... "Jacob C." wrote: I want to create a summary of data and have a solid formula (using such formula's as match, lookup, and vlookup). Unfortunately, when I try to drag these formulas, the arrays that they correspond to are not the ones I desire. For example, my original array is A10:A20 and want the next one to be A20:A30, but when I start dragging it goes to A11:A21. Even with multiple cells manually edited then dragged, it still doesn't want to do it in the format I want. Anyone?? |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Drag-filling a formula Array
INDIRECT returns the reference specified by a text string.
="A"&(ROW()*10+3)&":A"&(ROW()+1)*10+2 when entered in row 1 evaluates to A13:A22 so =INDIRECT(="A"&(ROW()*10+3)&":A"&(ROW()+1)*10+2) returns a reference to A13:A22 which you can then pass to your formula expecting that reference... Because of ROW() references changes with each row when copied down and evaluates to the ranges you want. Without looking at the formula you have this is all I can help you with. ------------------------------------- Pl. click ''''Yes'''' if this was helpful... "Jacob C." wrote: I didn't mean to over lap. My actual cells are E3:E12 then E13:E22, E23:E32 etc. etc. The formula you gave me didn't seem to help, though I don't really know how it works. Maybe with the specific cell numbers it will be more clear, but remember I am trying to put it into a lookup array. "Sheeloo" wrote: In row1 use INDIRECT("A"&(ROW()*10)&":A"&(ROW()+1)*10) whereever you had A10:A20 and then copy the formula down... Do note that A10:A20, A20:A30 are overlapping or did you mean A11:A20, A21:A30,...? ------------------------------------- Pl. click ''''Yes'''' if this was helpful... "Jacob C." wrote: I want to create a summary of data and have a solid formula (using such formula's as match, lookup, and vlookup). Unfortunately, when I try to drag these formulas, the arrays that they correspond to are not the ones I desire. For example, my original array is A10:A20 and want the next one to be A20:A30, but when I start dragging it goes to A11:A21. Even with multiple cells manually edited then dragged, it still doesn't want to do it in the format I want. Anyone?? |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Drag-filling a formula Array
Thanks alot. I think i got it.
"Sheeloo" wrote: INDIRECT returns the reference specified by a text string. ="A"&(ROW()*10+3)&":A"&(ROW()+1)*10+2 when entered in row 1 evaluates to A13:A22 so =INDIRECT(="A"&(ROW()*10+3)&":A"&(ROW()+1)*10+2) returns a reference to A13:A22 which you can then pass to your formula expecting that reference... Because of ROW() references changes with each row when copied down and evaluates to the ranges you want. Without looking at the formula you have this is all I can help you with. ------------------------------------- Pl. click ''''Yes'''' if this was helpful... "Jacob C." wrote: I didn't mean to over lap. My actual cells are E3:E12 then E13:E22, E23:E32 etc. etc. The formula you gave me didn't seem to help, though I don't really know how it works. Maybe with the specific cell numbers it will be more clear, but remember I am trying to put it into a lookup array. "Sheeloo" wrote: In row1 use INDIRECT("A"&(ROW()*10)&":A"&(ROW()+1)*10) whereever you had A10:A20 and then copy the formula down... Do note that A10:A20, A20:A30 are overlapping or did you mean A11:A20, A21:A30,...? ------------------------------------- Pl. click ''''Yes'''' if this was helpful... "Jacob C." wrote: I want to create a summary of data and have a solid formula (using such formula's as match, lookup, and vlookup). Unfortunately, when I try to drag these formulas, the arrays that they correspond to are not the ones I desire. For example, my original array is A10:A20 and want the next one to be A20:A30, but when I start dragging it goes to A11:A21. Even with multiple cells manually edited then dragged, it still doesn't want to do it in the format I want. Anyone?? |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Drag-filling a formula Array
Thanks for all the help so far, one more question. I've got the formula I
want, but for some reason I can't reference an array on another tab with the formula... Any chance there's a fix? 'Tab1'!&"A"&(ROW()*10+3)&":A"&(ROW()+1)*10+2 Doesn't work for me... "Sheeloo" wrote: INDIRECT returns the reference specified by a text string. ="A"&(ROW()*10+3)&":A"&(ROW()+1)*10+2 when entered in row 1 evaluates to A13:A22 so =INDIRECT(="A"&(ROW()*10+3)&":A"&(ROW()+1)*10+2) returns a reference to A13:A22 which you can then pass to your formula expecting that reference... Because of ROW() references changes with each row when copied down and evaluates to the ranges you want. Without looking at the formula you have this is all I can help you with. ------------------------------------- Pl. click ''''Yes'''' if this was helpful... "Jacob C." wrote: I didn't mean to over lap. My actual cells are E3:E12 then E13:E22, E23:E32 etc. etc. The formula you gave me didn't seem to help, though I don't really know how it works. Maybe with the specific cell numbers it will be more clear, but remember I am trying to put it into a lookup array. "Sheeloo" wrote: In row1 use INDIRECT("A"&(ROW()*10)&":A"&(ROW()+1)*10) whereever you had A10:A20 and then copy the formula down... Do note that A10:A20, A20:A30 are overlapping or did you mean A11:A20, A21:A30,...? ------------------------------------- Pl. click ''''Yes'''' if this was helpful... "Jacob C." wrote: I want to create a summary of data and have a solid formula (using such formula's as match, lookup, and vlookup). Unfortunately, when I try to drag these formulas, the arrays that they correspond to are not the ones I desire. For example, my original array is A10:A20 and want the next one to be A20:A30, but when I start dragging it goes to A11:A21. Even with multiple cells manually edited then dragged, it still doesn't want to do it in the format I want. Anyone?? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| help with filling in an array formula | Excel Discussion (Misc queries) | |||
| Filling Source Data Array with Decimal Values | Charts and Charting in Excel | |||
| Filling a List Box in Excel from an Array | Excel Discussion (Misc queries) | |||
| Filling excel entire row/column instead of single cell from an array | Excel Worksheet Functions | |||
| Filling an array with data | Excel Worksheet Functions | |||
Linear Mode
