Formula Error.. help !

[Rich]

=OFFSET(MATCH(i2,sheet2!$i:$i,0),1,-3)


is giving me an error... I cant see why..

 

[Bob Phillips]

Rich,

Look up OFFSET in help, you seem to be using it incorrectly. The arguments
are

OFFSET(reference,rows,cols,height,width)

Reference is the reference from which you want to base the offset.
Reference must be a reference to a cell or range of adjacent cells;
otherwise, OFFSET returns the #VALUE! error value.

Rows is the number of rows, up or down, that you want the upper-left cell
to refer to. Using 5 as the rows argument specifies that the upper-left cell
in the reference is five rows below reference. Rows can be positive (which
means below the starting reference) or negative (which means above the
starting reference).

Cols is the number of columns, to the left or right, that you want the
upper-left cell of the result to refer to. Using 5 as the cols argument
specifies that the upper-left cell in the reference is five columns to the
right of reference. Cols can be positive (which means to the right of the
starting reference) or negative (which means to the left of the starting
reference).

If rows and cols offset reference over the edge of the worksheet, OFFSET
returns the #REF! error value.

Height is the height, in number of rows, that you want the returned
reference to be. Height must be a positive number.

Width is the width, in number of columns, that you want the returned
reference to be. Width must be a positive number.
--

HTH


Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Rich]

Rich,

Look up OFFSET in help, you seem to be using it incorrectly. The arguments
are

OFFSET(reference,rows,cols,height,width)

Reference is the reference from which you want to base the offset.
Reference must be a reference to a cell or range of adjacent cells;
otherwise, OFFSET returns the #VALUE! error value.

Rows is the number of rows, up or down, that you want the upper-left cell
to refer to. Using 5 as the rows argument specifies that the upper-left cell
in the reference is five rows below reference. Rows can be positive (which
means below the starting reference) or negative (which means above the
starting reference).

Cols is the number of columns, to the left or right, that you want the
upper-left cell of the result to refer to. Using 5 as the cols argument
specifies that the upper-left cell in the reference is five columns to the
right of reference. Cols can be positive (which means to the right of the
starting reference) or negative (which means to the left of the starting
reference).

If rows and cols offset reference over the edge of the worksheet, OFFSET
returns the #REF! error value.

Height is the height, in number of rows, that you want the returned
reference to be. Height must be a positive number.

Width is the width, in number of columns, that you want the returned
reference to be. Width must be a positive number.
--

HTH


Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Thanks Bob,

I'm trying to use the MATCH function to find the cell I wish to reference
from. I had looked at the help on both. I'm comfortable with OFFSET, it
works perfectly when I give it a cell to reference, but I need to find a
customer number from a list, then use OFFSET to find the sales for that
customer.

I was adapting an answer given to me yesterday by Max...

 

[Paul]

I'm trying to use the MATCH function to find the cell I wish to reference
from. I had looked at the help on both. I'm comfortable with OFFSET, it
works perfectly when I give it a cell to reference, but I need to find a
customer number from a list, then use OFFSET to find the sales for that
customer.

I was adapting an answer given to me yesterday by Max...

OFFSET needs a cell (or range) reference as its first argument. MATCH
returns a position within a range, not a cell reference. (For example, if it
finds a match in the fourth cell of the range it returns 4.) Therefore you
cannot use this function as the first argument of OFFSET.

Subsequent arguments of OFFSET are numbers, so MATCH could be used to return
a value to any of these.

If you really need to calculate the first argument for OFFSET, I suggest you
look at INDEX (rather than MATCH), as this function can return a reference.

 

[Bob Phillips]

Paul,

I Think that MATCH is fine, but Rich should use it within INDEX rather than
OFFSET, circa

=INDEX($2:$2,1,MATCH(I2,Sheet2!$I:$I,0))

I may have the rows and columns wrong here, haven't checked it


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Max]

I gave Rich this example in response to his post in .misc yesterday
: http://tinyurl.com/2z7g5

Rich: Why didn't you reply in the thread above in .misc ?

It would have made it much easier for others to step in
to help your problem adapting the example I gave to suit,
with the background of your original post and details of my example.

Could you provide a description of your data layout,
which are the actual columns containing the CustRefs in each workbook, etc?

The more specifics you can provide,
the more specific a workable solution would be given.