I'm looking for a function which will do the following:

(1) Check each cell in "Sheet1", column "J" for a value of 8, 10, or 12.5

Example: If cell "J5" on "Sheet1" = 8, then add the value of "Sheet1" cell
"I5" to "Sheet2" cell "B17", etc.

(2) If the value is 8, then add the value of the neighboring cell in column
"I" to cell "B17" in "Sheet 2"
(3) If the value is 10, then add the value of the neighboring cell in column
"I" to cell "B18" in "Sheet 2"
(4) If the value is 12.5, then add the value of the neighboring cell in
column "I" to cell "B19" in "Sheet 2"

Any help appreciated.

Try this:

=IF(J5=8,I5+Sheet2!$B$17,IF(J5=10,I5+Sheet2!$B$18, IF(J5=12.5,I5+Sheet2!
$B$19,0)))

This assumes that the formula is in Sheet1 - if it is not, then change
the references to J5 and I5 to Sheet1!J5 and Sheet1!I5 respectively.
I've assumed that you want a zero returned if J5 does not contain any
of the 3 values.

Copy it down as required.

Hope this helps.

Pete

On Dec 7, 3:31*pm, TomB wrote:
I'm looking for a function which will do the following:

(1) Check each cell in "Sheet1", column "J" for a value of 8, 10, or 12.5

Example: If cell "J5" on "Sheet1" = 8, then add the value of "Sheet1" cell
"I5" to "Sheet2" cell "B17", etc.

(2) If the value is 8, then add the value of the neighboring cell in column
"I" to cell "B17" in "Sheet 2"
(3) If the value is 10, then add the value of the neighboring cell in column
"I" to cell "B18" in "Sheet 2"
(4) If the value is 12.5, then add the value of the neighboring cell in
column "I" to cell "B19" in "Sheet 2"

Any help appreciated.

Pete_UK wrote...
Try this:

=IF(J5=8,I5+Sheet2!$B$17,IF(J5=10,I5+Sheet2!$B$18 ,
IF(J5=12.5,I5+Sheet2!$B$19,0)))
....

You could eliminate some redundancy with

=I5+IF(J5=8,Sheet2!$B$17,IF(J5=10,Sheet2!$B$18,
IF(J5=12.5,Sheet2!$B$19,-I5)))

Then again this looks like INDEX/MATCH might make more sense since the
Sheet2 cells are adjacent and in order.

It gives a Circular Reference error, even when I try it testing one value only.

"Pete_UK" wrote:

Try this:

=IF(J5=8,I5+Sheet2!$B$17,IF(J5=10,I5+Sheet2!$B$18, IF(J5=12.5,I5+Sheet2!
$B$19,0)))

This assumes that the formula is in Sheet1 - if it is not, then change
the references to J5 and I5 to Sheet1!J5 and Sheet1!I5 respectively.
I've assumed that you want a zero returned if J5 does not contain any
of the 3 values.

Copy it down as required.

Hope this helps.

Pete

On Dec 7, 3:31 pm, TomB wrote:
I'm looking for a function which will do the following:

(1) Check each cell in "Sheet1", column "J" for a value of 8, 10, or 12.5

Example: If cell "J5" on "Sheet1" = 8, then add the value of "Sheet1" cell
"I5" to "Sheet2" cell "B17", etc.

(2) If the value is 8, then add the value of the neighboring cell in column
"I" to cell "B17" in "Sheet 2"
(3) If the value is 10, then add the value of the neighboring cell in column
"I" to cell "B18" in "Sheet 2"
(4) If the value is 12.5, then add the value of the neighboring cell in
column "I" to cell "B19" in "Sheet 2"

Any help appreciated.

.

Which cell did you put the formula in?

Pete

On Dec 8, 12:20*pm, TomB wrote:
It gives a Circular Reference error, even when I try it testing one value only.



"Pete_UK" wrote:
Try this:

=IF(J5=8,I5+Sheet2!$B$17,IF(J5=10,I5+Sheet2!$B$18, IF(J5=12.5,I5+Sheet2!
$B$19,0)))

This assumes that the formula is in Sheet1 - if it is not, then change
the references to J5 and I5 to Sheet1!J5 and Sheet1!I5 respectively.
I've assumed that you want a zero returned if J5 does not contain any
of the 3 values.

Copy it down as required.

Hope this helps.

Pete

On Dec 7, 3:31 pm, TomB wrote:
I'm looking for a function which will do the following:

(1) Check each cell in "Sheet1", column "J" for a value of 8, 10, or 12.5

Example: If cell "J5" on "Sheet1" = 8, then add the value of "Sheet1" cell
"I5" to "Sheet2" cell "B17", etc.

(2) If the value is 8, then add the value of the neighboring cell in column
"I" to cell "B17" in "Sheet 2"
(3) If the value is 10, then add the value of the neighboring cell in column
"I" to cell "B18" in "Sheet 2"
(4) If the value is 12.5, then add the value of the neighboring cell in
column "I" to cell "B19" in "Sheet 2"

Any help appreciated.

.- Hide quoted text -

- Show quoted text -