How deep of Nested IF, AND, OR statements can be utilized

**WestCoastBoy** · 02-22-2023, 02:25 PM

Good Day:

I find I've sort of backed myself into a corner, and like anything after awhile it all starts to look the same.
Note*** Everything below Line 5 works as it should, this is just for above line 5.
C15 is also a drop down box

Background of the file included.
A2 is a drop down box, you choose 1 of 5 choices.
B3 is also a drop down box, and you choose 1 of 4 choices
I am trying to determine how to formulate the IF statement that:
if in A2 a choice is made, THEN the choice in B3 will determine the price and Place it in D3

A2 selections have a specific pricing sheet attached to each of the choices
So, I am aware my current "IF" statement is incomplete.

Is there a better way to do this?, will I be typing out an IF statement for a week?
I need to have all 4 B3 statements contained into each of the 5 A2 Choices
What I have so far, I've separated for ease of reading
This part works, it's when I try to add additional B2 items that I get whacked

=IF(ISBLANK(B3),"",IF(AND(A2="Bank 1",B3="LLR-"),"$835.00",
IF(AND(A2="Bank 2",B3="LLR-"),"$1000.00",
IF(AND(A2="Bank 3",B3="LLR-"),"$1000.00",
IF(AND(A2="Bank 4",B3="LLR-"),"$835.00",
IF(AND(A2="Bank 5",B3="LLR-"),"$850.00",
IF(AND(A2="Standard Fees",B3="LLR-"),"$1000.00")))))))

Bonus points if you can split B2 into two Cells, that will accept text.

I am available by email, or even by phone if this is something you can assist with, and might also post into the Commercial Services forum to pay to get it done right.

**TMS** · 02-22-2023, 03:13 PM

Create a matrix (on another sheet) with the A2 values down, say, A2 down, and the B3 values in B1 across. Then complete the matrix. You can now use INDEX/MATCH/MATCH to return the relevant value.

Please note that putting numbers in quotes and/or manually typing a dollar sign makes the value Text, not a numeric value.

**JohnTopley** · 02-22-2023, 03:35 PM

First

Formula:

Please Login or Register  to view this content.

Your formula returns TEXT and D14?D15 will error

Consider using a "Lookup" table rather than multiple IFs

in F3

Formula:

Please Login or Register  to view this content.

**WestCoastBoy** · 02-23-2023, 10:37 AM

Thanks for a potential solution, While this would probably be great for a single user, the caveat, and I guess I should have mentioned is the following
This excel piece is part of an invoice, that invoice is a word doc, so this gets filled out by 1 user, then embeds it into the invoice.
The invoice completed by the 1 user, then gets passed off to an analyst and after the analyst does whatever work they require to do, "if" they need to make changes to the amount charged, they double click the excel portion of the invoice, it pops out of the doc, they make whatever changes they require, and then pop it back in.

The solution you've provided while great for a single user, I don't think would work if the analyst (could be 1 of a dozen), doesn't have the array to pull the info from correct?
Trying to keep things as simple as possible, without a lot of other sheet references, so this is why the "IF" statements were proposed...

Thanks

**WestCoastBoy** · 02-23-2023, 10:46 AM

Originally Posted by JohnTopley

First

Formula:

Please Login or Register  to view this content.

Your formula returns TEXT and D14?D15 will error

Consider using a "Lookup" table rather than multiple IFs

in F3

Formula:

Please Login or Register  to view this content.

The "IF" statements above were all edited to remove identifying info, but from what I've since been told, thats correct, TEXT is being returned, because of the quotes/dollar sign etc.

Where would the lookup table be located, and would others need to have access to it in order for changes to occur.
I've added a reply (it didn't quote, but it's there), about how the process works, so would the analyst also need access to that lookup table in order to add/take away

I'm open to various solutions if need be, so for now, just concerned about being able to match up the bank1 through 5 to the multiple choices. ..there are 4 choices for B3
I may have to look at not pre-populating the Fee in D3, and enter it manually for now

Hopefully I've explained things, funny it works in my head LOL, but needed the professionals here to tweak it.

**JohnTopley** · 02-23-2023, 04:28 PM

If you really think mutiple IF statements are better the "lookup" solution then good luck.

You really expect users to go throght a complex IF statement and make amendements?

Your description of your process - a recipe for disaster

.

The table can be placed anywhere in workbook.

**TMS** · 02-23-2023, 05:28 PM

I'm grateful to John for going to the trouble of producing a sample workbook based on your OP.

It was what I had in mind, although I would have advocated putting the lookup table on a separate worksheet. That said, you could hide the table but you'd also need to protect it so it can't (easily) be corrupted.

Now you're going to need to educate me. I have never, as far as I can recall, used an embedded spreadsheet in a word document.

Now, here's the thing …

A range does not exist in isolation, it is part of worksheet; a worksheet does not exist in isolation, it is part of a workbook. So, I assume that, rather than a range, it is a workbook that is embedded in your word document. So, in theory, when they double click the excel portion of the invoice, they have access to the embedded workbook. Happy to be corrected, not my field of experience. I'm also guessing that means, if there are multiple copies of the invoice document, there are multiple embedded workbooks.

Anyway, whether you do this with a lookup table or nested IFs, I suspect that maintenance of this setup is going to be a nightmare.

**WestCoastBoy** · 02-24-2023, 11:10 AM

Originally Posted by JohnTopley

If you really think mutiple IF statements are better the "lookup" solution then good luck.

You really expect users to go throght a complex IF statement and make amendements?

Your description of your process - a recipe for disaster

.

The table can be placed anywhere in workbook.

First of all, I didn't think multiple "IF" statements are better, I didn't know, hence why I posted the question, so thanks for your input, but you didn't have to be a **** about it...but that's fine, only working here with what I have, and what I know, which is why the "maybe someone has a better solution" tag...if the supplied solutions don't or can't work, then that's fine, will figure out something else
But hey, again thanks for your Input...

**WestCoastBoy** · 02-24-2023, 11:21 AM

Originally Posted by TMS

I'm grateful to John for going to the trouble of producing a sample workbook based on your OP.

It was what I had in mind, although I would have advocated putting the lookup table on a separate worksheet. That said, you could hide the table but you'd also need to protect it so it can't (easily) be corrupted.

Now you're going to need to educate me. I have never, as far as I can recall, used an embedded spreadsheet in a word document.

Now, here's the thing …

A range does not exist in isolation, it is part of worksheet; a worksheet does not exist in isolation, it is part of a workbook. So, I assume that, rather than a range, it is a workbook that is embedded in your word document. So, in theory, when they double click the excel portion of the invoice, they have access to the embedded workbook. Happy to be corrected, not my field of experience. I'm also guessing that means, if there are multiple copies of the invoice document, there are multiple embedded workbooks.

Anyway, whether you do this with a lookup table or nested IFs, I suspect that maintenance of this setup is going to be a nightmare.

Thanks, and you're correct, double clicking they will have access to the embedded workbook. and only 2 copies, but get what you're saying, the set up is already a nightmare, which is why I wondered if there was a better way, once it's set up, then nothing on the back end would change other than once a year, maybe a pricing change, as the scope of the work (b3) never changes. and Banks (A2) rarely change.

Appreciate the input, I find "most" to be very helpful in these forums, especially with the lesser experienced folk...I'm just trying to fix a broken part of something, and thought it was possibly simpler than what I was thinking

So saying that, what if an excel invoice or word invoice could be configured to do that, I mean am I looking at the same situation using a pre made configurable Excel Invoice template? or if a Word configured Invoice...Just asking, as it's looking as there's no way around the lookup table/array aspect right now

**JohnTopley** · 02-24-2023, 11:32 AM

Have you considered setting up the invoice in Excel (rather than Word) and creating an Excel_based PDF file as output?

The "better" solution only relates to replacing the IF with a table but I think it is fair to say we (I !) don't fully understand how this integrates with the Invoice generation.

Are you able to post a more complete represention i.e Word document + the Excel elements.

**TMS** · 02-24-2023, 03:24 PM

Ok, a few thoughts.

1. If there are only two copies, the simplest approach might be to maintain a "master" copy controlled (by you?) centrally which is updated and distributed on an as required basis.
2. There are probably ways of managing the distribution, for example, in the workbook open event handler, checking for the existence of an updated copy in a shared folder, but I think the effort of establishing the update process would not be worthwhile.
3. Another option would be to use the workbook open event handler to download the lookup table from a central location every time the invoice workbook is opened. Again, for two copies, it might not be worth it … although I think this would be simpler to implement.
4. I think we could develop a "stand-alone" workbook which could be the base for the embedded workbook and the core of the "master" copy.

How deep of Nested IF, AND, OR statements can be utilized

LinkBack

Thread Tools

Rate This Thread

Display

How deep of Nested IF, AND, OR statements can be utilized

Re: How deep of Nested IF, AND, OR statements can be utilized

Re: How deep of Nested IF, AND, OR statements can be utilized

Re: How deep of Nested IF, AND, OR statements can be utilized

Re: How deep of Nested IF, AND, OR statements can be utilized

Re: How deep of Nested IF, AND, OR statements can be utilized

Re: How deep of Nested IF, AND, OR statements can be utilized

Re: How deep of Nested IF, AND, OR statements can be utilized

Re: How deep of Nested IF, AND, OR statements can be utilized

Re: How deep of Nested IF, AND, OR statements can be utilized

Re: How deep of Nested IF, AND, OR statements can be utilized

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Nested IF/THEN Formula - 5 Deep I believe

[SOLVED] Nested if statements containing multiple and statements

External Links nested more than one deep

questions with if statements and nested if statements

Nested If statements / logic statements

Nested IF statements or CHOOSE statements needed for multiple conditions formula

Can Check Boxes be Utilized in this Way?

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions