Tricky if function

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

hlpme - 19 Mar 2008 17:41 GMT

I am looking to create an IF function, but I need to draw information off
another sheet, is this possible? And how do I create the function with
between values too?

Example of what I am trying to do:

If Sheet 1 A1 is between 200- 300, I need the values from Sheet 2 A3, A4, A5
to be inserted into Sheet 1 B2, B3, B4.

If Sheet 1 A1 is between 300-400, I need the values from Sheet 2 B3, B4, B5
to be inserted in the Sheet 1 B2, B3, B4.

If Sheet 1 A1 is between 400-500, I need the values from Sheet 2 C3, C4, C5
to be inserted in to Sheet 1 B2, B3, B4.

Any and all help is much appreciated!!

THANKS IN ADVANCE! Have a wonderful day!

Reply to this Message

Gary''s Student - 19 Mar 2008 17:53 GMT

In another cell, say H10 enter:

=INT(A1/100)-1

then in B2 thru B4 enter:

=CHOOSE(H10,Sheet2!A3,Sheet2!A4,Sheet2!A5)
=CHOOSE(H10,Sheet2!B3,Sheet2!B4,Sheet2!B5)
=CHOOSE(H10,Sheet2!C3,Sheet2!C4,Sheet2!C5)

Signature

Gary''s Student - gsnu200774

> I am looking to create an IF function, but I need to draw information off
> another sheet, is this possible? And how do I create the function with
[quoted text clipped - 14 lines]
>
> THANKS IN ADVANCE! Have a wonderful day!

Reply to this Message

JP - 19 Mar 2008 18:06 GMT

One way:

=IF(AND(Sheet1!$A$1>200,Sheet1!$A$1<300),Sheet2!A3,IF(AND(Sheet1!$A
$1>300,Sheet1!$A$1<400),Sheet2!B3,IF(AND(Sheet1!$A$1>400,Sheet1!$A
$1<500),Sheet2!C3,"")))

Put this formula in Sheet1!B2 and fill down to B4. Assuming Sheet1 and
Sheet2 are the actual names of your sheets, of course.

HTH,
JP

Reply to this Message