New problem with new "IF"

  • Thread starter Thread starter gbeard
  • Start date Start date
G

gbeard

=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,
 
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))+INDIRECT("'"&B$1&"'!"&C$4&ROW(
A2)))
 
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,
 
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,
 
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!
-------------------------------------------------------------------






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,
 
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!
==============================================

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!
-------------------------------------------------------------------






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,
 
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
 
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 !
--------------------------------------------------------------------

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
 
You're quite welcome.
--

Regards,

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

RD,
It worked perfectly!

Thanks for the help
 
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Miss-wrote previous "IF" problem 3
Set non-continuous PrintArea via Function / Name 0
What type of "lookup" 3
INDIRECT(ADDRESS) inside a VLOOKUP 13
Vlookup and return sheet name also 2
Scrabble Value calculation for Welsh words 0
With F1 value, look at A, if match or partial match return B 5
SMALL(IF( across multiple worksheets 20

Back
Top