=IF(INDIRECT("'"&B$1&"'!"&"C$2"&ROW(A2))+INDIRECT( "'"&B$1&"'!"&"C$4"&ROW(A2))<=$B$3,(INDIRECT("'"&B$ 1&"'!"&C$2&ROW(A2))+INDIRECT("'"&B$1&"'!"&C$4&ROW( A2))),)

I'm trying to get this IF to return a blank cell if the result is <=$b$3.
But, it's returning every result whether it's < or $B$3.
This is returning the price of 2 columns if the total price is less than the
customer wants to pay. The INDIRECTS are pointing to cells with info from
the customer (how many parts they're purchasing, how soon they need them,
what they want to pay and also what area they are in). Based on the area
they are in another similar formula returns the vendors who offer parts for
the price they want to pay and this formula is in the column next to the
vendor name and shows their price. But right now it's showing every vendors
price, which I could just look on the data sheet to find that information.


Please let me know if you can help,
--
Gary Beard

You should construct your formula with simple cell references first, to make
sure your logic is correct, before you complicate the references and lose
track of exactly what you're trying to accomplish.

Your formula equates to something like this with test addresses:

=IF(A2+B2<=B3,A2+B2,)

Don't know why you're ending it without a final argument?

From what you're saying, I would think that you want something like this:

=IF(A2+B2<=B3,"",A2+B2)

Which would make your formula look something like this:

=IF(INDIRECT("'"&B$1&"'!"&"C$2"&ROW(A2))+INDIRECT( "'"&B$1&"'!"&"C$4"&ROW(A2)
)<=$B$3,"",INDIRECT("'"&B$1&"'!"&C$2&ROW(A2))+INDI RECT("'"&B$1&"'!"&C$4&ROW(
A2)))

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"gbeard" wrote in message
...

=IF(INDIRECT("'"&B$1&"'!"&"C$2"&ROW(A2))+INDIRECT( "'"&B$1&"'!"&"C$4"&ROW(A2)
)<=$B$3,(INDIRECT("'"&B$1&"'!"&C$2&ROW(A2))+INDIRE CT("'"&B$1&"'!"&C$4&ROW(A2
))),)

I'm trying to get this IF to return a blank cell if the result is <=$b$3.
But, it's returning every result whether it's < or $B$3.
This is returning the price of 2 columns if the total price is less than
the
customer wants to pay. The INDIRECTS are pointing to cells with info from
the customer (how many parts they're purchasing, how soon they need them,
what they want to pay and also what area they are in). Based on the area
they are in another similar formula returns the vendors who offer parts
for
the price they want to pay and this formula is in the column next to the
vendor name and shows their price. But right now it's showing every
vendors
price, which I could just look on the data sheet to find that information.


Please let me know if you can help,
--
Gary Beard

RD,
That formula returned a blank for every response. Even when I set the
threshold high enough to make each row return a response, all I got was a
blank.

I appreciate your insight and your help,

--
Gary Beard

RD,
I noticed I miss wrote my first explanation. I want a blank cell if the
result is B3 not <B3. I want it to return the sum if it's <B3.

Sorry,

--
Gary Beard

Break up the references and try each one separately to insure that they *do*
actually return the value in the cell.

On my test Sheet1,
B1 = sheet2
C2 = a
C4 = b
On Sheet2,
A2 contains the number 555.
B2 contains the number 222

I took this:
=IF(INDIRECT("'"&B$1&"'!"&"C$2"&ROW(A2))

Revised it to this:
=INDIRECT(B1&"!"&C$2&ROW(A2))

And got the return of 555.

I did the same with the second half :
+INDIRECT("'"&B$1&"'!"&"C$4"&ROW(A2))
And revised it to:
=INDIRECT(B$1&"!"&C$4&ROW(A2))

And got the return of 222.

Combining them:

=INDIRECT(B1&"!"&C$2&ROW(A2))+INDIRECT(B$1&"!"&C$4 &ROW(A2))

Gave me a return of 777.

So ... for me ... this formula works by returning the contents of the cells
I referenced.

I don't have any idea what you have in the cells you're referencing.

You should check out your formula, piecemeal, and see if you get exactly
what you expect.

Post back with your results.
--


Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit!
-------------------------------------------------------------------






"gbeard" wrote in message
m...
RD,
I noticed I miss wrote my first explanation. I want a blank cell if the
result is B3 not <B3. I want it to return the sum if it's <B3.

Sorry,

--
Gary Beard

Don't know why I didn't see this before, but I just happened to notice that
you *have* quotes around C2 and C4 in the first half of the formula, and
*not* in the second half.

*Eliminate* the quotes from C2 and C4!

--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

"RagDyer" wrote in message
...
Break up the references and try each one separately to insure that they *do*
actually return the value in the cell.

On my test Sheet1,
B1 = sheet2
C2 = a
C4 = b
On Sheet2,
A2 contains the number 555.
B2 contains the number 222

I took this:
=IF(INDIRECT("'"&B$1&"'!"&"C$2"&ROW(A2))

Revised it to this:
=INDIRECT(B1&"!"&C$2&ROW(A2))

And got the return of 555.

I did the same with the second half :
+INDIRECT("'"&B$1&"'!"&"C$4"&ROW(A2))
And revised it to:
=INDIRECT(B$1&"!"&C$4&ROW(A2))

And got the return of 222.

Combining them:

=INDIRECT(B1&"!"&C$2&ROW(A2))+INDIRECT(B$1&"!"&C$4 &ROW(A2))

Gave me a return of 777.

So ... for me ... this formula works by returning the contents of the cells
I referenced.

I don't have any idea what you have in the cells you're referencing.

You should check out your formula, piecemeal, and see if you get exactly
what you expect.

Post back with your results.
--


Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit!
-------------------------------------------------------------------






"gbeard" wrote in message
m...
RD,
I noticed I miss wrote my first explanation. I want a blank cell if the
result is B3 not <B3. I want it to return the sum if it's <B3.

Sorry,

--
Gary Beard

So now you find the problem! <g I missed it myself but I
didn't test anything!

I still stand by the vote of confidence I gave you in his
other thread!

Biff

-----Original Message-----
Don't know why I didn't see this before, but I just
happened to notice that
you *have* quotes around C2 and C4 in the first half of
the formula, and
*not* in the second half.

*Eliminate* the quotes from C2 and C4!

--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all
may benefit!
==============================================

"RagDyer" wrote in message
...
Break up the references and try each one separately to
insure that they *do*
actually return the value in the cell.

On my test Sheet1,
B1 = sheet2
C2 = a
C4 = b
On Sheet2,
A2 contains the number 555.
B2 contains the number 222

I took this:
=IF(INDIRECT("'"&B$1&"'!"&"C$2"&ROW(A2))

Revised it to this:
=INDIRECT(B1&"!"&C$2&ROW(A2))

And got the return of 555.

I did the same with the second half :
+INDIRECT("'"&B$1&"'!"&"C$4"&ROW(A2))
And revised it to:
=INDIRECT(B$1&"!"&C$4&ROW(A2))

And got the return of 222.

Combining them:

=INDIRECT(B1&"!"&C$2&ROW(A2))+INDIRECT(B$1&"!"&C$ 4&ROW
(A2))

Gave me a return of 777.

So ... for me ... this formula works by returning the
contents of the cells
I referenced.

I don't have any idea what you have in the cells you're
referencing.

You should check out your formula, piecemeal, and see if
you get exactly
what you expect.

Post back with your results.
--


Regards,

RD
----------------------------------------------------------
----------
Please keep all correspondence within the Group, so all
may benefit!
----------------------------------------------------------
---------






"gbeard" wrote in message
om...
RD,
I noticed I miss wrote my first explanation. I want a
blank cell if the
result is B3 not <B3. I want it to return the sum if
it's <B3.

Sorry,

--
Gary Beard


.

RD,
Thanks bud...I'll try it and get back to you.

--
Gary Beard

RD,
It worked perfectly!

Thanks for the help
--
Gary "Just when I thought I was getting the hang of Excel" Beard

Biff,
Thanks for your help...I really appreciate it!

--
Gary Beard

Don't know what's been happening with the servers lately, but this post:

<<"So now you find the problem! <g I missed it myself but I
didn't test anything!
I still stand by the vote of confidence I gave you in his
other thread!
Biff"

This is the first post that I can see from you in regards to this thread or
the OPs first thread.

Since Gary's thanking you for something, I guess he can see your
submissions.

I notice a greater number of posts appearing that don't make any sense,
meaning that more and more of them are not coming through all of the MS
servers, and the threads are being fragmented.
I've posted suggestions to several threads, where I could only see the OP
through the post of another responder to that thread.

And you say that you gave me a vote of confidence?<g

Gee ... that's nice.
Too bad I missed that one!<vbg
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------

"Biff" wrote in message
...
So now you find the problem! <g I missed it myself but I
didn't test anything!

I still stand by the vote of confidence I gave you in his
other thread!

Biff

-----Original Message-----
Don't know why I didn't see this before, but I just
happened to notice that
you *have* quotes around C2 and C4 in the first half of
the formula, and
*not* in the second half.

*Eliminate* the quotes from C2 and C4!

--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all
may benefit!
==============================================

"RagDyer" wrote in message
...
Break up the references and try each one separately to
insure that they *do*
actually return the value in the cell.

On my test Sheet1,
B1 = sheet2
C2 = a
C4 = b
On Sheet2,
A2 contains the number 555.
B2 contains the number 222

I took this:
=IF(INDIRECT("'"&B$1&"'!"&"C$2"&ROW(A2))

Revised it to this:
=INDIRECT(B1&"!"&C$2&ROW(A2))

And got the return of 555.

I did the same with the second half :
+INDIRECT("'"&B$1&"'!"&"C$4"&ROW(A2))
And revised it to:
=INDIRECT(B$1&"!"&C$4&ROW(A2))

And got the return of 222.

Combining them:

=INDIRECT(B1&"!"&C$2&ROW(A2))+INDIRECT(B$1&"!"&C$ 4&ROW
(A2))

Gave me a return of 777.

So ... for me ... this formula works by returning the
contents of the cells
I referenced.

I don't have any idea what you have in the cells you're
referencing.

You should check out your formula, piecemeal, and see if
you get exactly
what you expect.

Post back with your results.
--


Regards,

RD
----------------------------------------------------------
----------
Please keep all correspondence within the Group, so all
may benefit!
----------------------------------------------------------
---------






"gbeard" wrote in message
om...
RD,
I noticed I miss wrote my first explanation. I want a
blank cell if the
result is B3 not <B3. I want it to return the sum if
it's <B3.

Sorry,

--
Gary Beard


.

You're quite welcome.
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------

"gbeard" wrote in message
...
RD,
It worked perfectly!

Thanks for the help
--
Gary "Just when I thought I was getting the hang of Excel" Beard

Hi Rd!

Yeah, there's something wrong with the "groups".

I normally access through the web interface but some of the groups aren't
available there. .excel, .misc, .newusers, .worksheet.functions don't come
up. So I'm using a newsreader at this time.

There are an awful lot of fragmented posts! Replies coming up as individual
threads.

I still stand by the vote of confidence I gave you in his other thread!

Well, that's a compliment and a joke at the same time.

I guess you had "to be there" to get it.

Biff

"RagDyeR" wrote in message
...
Don't know what's been happening with the servers lately, but this post:

<<"So now you find the problem! <g I missed it myself but I
didn't test anything!
I still stand by the vote of confidence I gave you in his
other thread!
Biff"

This is the first post that I can see from you in regards to this thread
or
the OPs first thread.

Since Gary's thanking you for something, I guess he can see your
submissions.

I notice a greater number of posts appearing that don't make any sense,
meaning that more and more of them are not coming through all of the MS
servers, and the threads are being fragmented.
I've posted suggestions to several threads, where I could only see the OP
through the post of another responder to that thread.

And you say that you gave me a vote of confidence?<g

Gee ... that's nice.
Too bad I missed that one!<vbg
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------

"Biff" wrote in message
...
So now you find the problem! <g I missed it myself but I
didn't test anything!

I still stand by the vote of confidence I gave you in his
other thread!

Biff

-----Original Message-----
Don't know why I didn't see this before, but I just
happened to notice that
you *have* quotes around C2 and C4 in the first half of
the formula, and
*not* in the second half.

*Eliminate* the quotes from C2 and C4!

--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all
may benefit!
==============================================

"RagDyer" wrote in message
.. .
Break up the references and try each one separately to
insure that they *do*
actually return the value in the cell.

On my test Sheet1,
B1 = sheet2
C2 = a
C4 = b
On Sheet2,
A2 contains the number 555.
B2 contains the number 222

I took this:
=IF(INDIRECT("'"&B$1&"'!"&"C$2"&ROW(A2))

Revised it to this:
=INDIRECT(B1&"!"&C$2&ROW(A2))

And got the return of 555.

I did the same with the second half :
+INDIRECT("'"&B$1&"'!"&"C$4"&ROW(A2))
And revised it to:
=INDIRECT(B$1&"!"&C$4&ROW(A2))

And got the return of 222.

Combining them:

=INDIRECT(B1&"!"&C$2&ROW(A2))+INDIRECT(B$1&"!"&C $4&ROW
(A2))

Gave me a return of 777.

So ... for me ... this formula works by returning the
contents of the cells
I referenced.

I don't have any idea what you have in the cells you're
referencing.

You should check out your formula, piecemeal, and see if
you get exactly
what you expect.

Post back with your results.
--


Regards,

RD
----------------------------------------------------------
----------
Please keep all correspondence within the Group, so all
may benefit!
----------------------------------------------------------
---------






"gbeard" wrote in message
. com...
RD,
I noticed I miss wrote my first explanation. I want a
blank cell if the
result is B3 not <B3. I want it to return the sum if
it's <B3.

Sorry,

--
Gary Beard


.