Trying to set up a cross reference chart, best way to copy formulas?

[Bryan.Cook]

My source data looks like this:
On the <i>Changes</i> sheet I have information in Columns C through H.
C and D contain the two objects to cross reference in the chart (think
of a mileage chart, such as this one
http://www.hm-usa.com/distance/usa.html ).
Column E Contains the current value, F the minimum value, G the Max
value, and H the mean value.

Data starts in row 2.

I want to reference the data in a chart on a new sheet

A B C D
1 Min(1) Current(1) Min(3) Current(3)
2 Max(1) Average(1) Max(3) Average(3)
3 Min(2) Current(2) Min(4) Current(4)
4 Max(2) Average(2) Max(4) Average(4)

Where:
Min1 refers to Cell $F2
Min2 refers to Cell $F3

Max1 refers to $G2
Max2 refers to $G3

etc...

The chart will be rather large (I've got several hundred data points).
However, I can't drag the formula, because if I type int he formulas
for A1:B2 (The full data set for my frist cross reference) and drag it
down to A3:B4 it will have Min2 refer to cell F4, rather than F3.
Likewise, if I drag it across to C12, it will have Min3 refer to F2,
rather than F4.

Any way to easily copy a formula like this, or am i stuck handcoding?

 

[Bryan.Cook]

Or better yet, is there a way to combine Hlookup and Vlookup to build
my cross reference chart?

So that if my 2 objects are in A2 and B1 cell b2 could lookup A2 and B1
and match it to values in columns c and d of my other sheet?

 

[Guest]

There are some examples of INDEX and MATCH here:
http://www.contextures.com/xlFunctions03.html
It looks like they can be applied to your case.

Hope this helps,
Miguel.