Drag-filling a formula Array

[Jacob C.]

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??

 

[Sheeloo]

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,...?

 

[Jacob C.]

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]

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.

 

[Jacob C.]

Thanks alot. I think i got it.

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.

 

[Jacob C.]

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...