Forum Discussion
Giant =IF statement
FYI: The "3 condition condition", and my questions about it, came from your first post, where you gave this as your example:
Column A row 7, 32, and 56 all have the value CCZJV-001. Based on this value I need columns B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q to all output different information.
Anyway, based on what you're now saying, I will suggest that you work to rid yourself of your C++ background---one of the biggest hindrances to learning Excel can be a background in a programming language; it tempts you (I have experienced it, and seen it many times) to think in programming/procedural terms and overlook the power of Excel.'s built-in functions.
From how you're now describing things, I'm going to suggest that you look into VLOOKUP, XLOOKUP, the combination of INDEX and MATCH--any one of those methods being a possible solution. These are ways to access a table and extract information from it. What you've described sounds like an ideal application for one of those, and a LOT easier to write than a complex IF conditional.
To take your partial example from the most recent post, you could get CCZJV-003 from column A, and then use it as the reference to a table and get the values for columns B through Q from that table.
Here's a good link--there are also many YouTube videos--on VLOOKUP and how to use it. https://exceljet.net/excel-functions/excel-vlookup-function
I am having to start over typing this due to internet explore crashing.
First thing is, you are a very smart individual. Much smarter than I; the questions you ask are a little over my head but I am trying to respond appropriately.
To give you a general idea, this is a database I am creating that I will copy into multiple documents. Each document will access different information separately. For now I only want to work on the single document in use.
The main benefit in this document is actually in the 'Shipment Tracking Log'. This gets updated every time something is shipped out (Daily). I want to be able to type in the Waste Profile Number and that's it. Everything else is populated.
To answer your first question no. It should only depend on 1 row. I'm not sure I understand where the 3 rows is coming from.
Yes I will have profiles repeated multiple times on the document. The target could appear in 1 row or 50 rows. It does not matter as long as the row has the proper information. For example,
The day is March 15th of 2017, I shipped off CCZJV-003, I type this in, in L48. Now I need F48 to reference the database and tell me exactly what the waste is, I want it to reference todays date in J48, I want it to reference the Transportation Company Name in N48, and so on. The next day the same waste is shipped again, now I want to input the waste profile in L49, the only thing I want to change is the date shipped.
I see it working as a nested if statement (I come form a C# background)
if ( x == CCZJV-001)
{
console.WriteLine(A14;"insert information here")
}
if x==CCZJV-002
and so on and on. This way just does not seem optimal to me, and I cannot get the code right in excel.
I.E.
=IF('Waste Profile Log'!A:Q=CCZJV-1,'Waste Profile Log'!A:Q=Sprint Waste, Error)
FYI: The "3 condition condition", and my questions about it, came from your first post, where you gave this as your example:
Column A row 7, 32, and 56 all have the value CCZJV-001. Based on this value I need columns B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q to all output different information.
Anyway, based on what you're now saying, I will suggest that you work to rid yourself of your C++ background---one of the biggest hindrances to learning Excel can be a background in a programming language; it tempts you (I have experienced it, and seen it many times) to think in programming/procedural terms and overlook the power of Excel.'s built-in functions.
From how you're now describing things, I'm going to suggest that you look into VLOOKUP, XLOOKUP, the combination of INDEX and MATCH--any one of those methods being a possible solution. These are ways to access a table and extract information from it. What you've described sounds like an ideal application for one of those, and a LOT easier to write than a complex IF conditional.
To take your partial example from the most recent post, you could get CCZJV-003 from column A, and then use it as the reference to a table and get the values for columns B through Q from that table.
Here's a good link--there are also many YouTube videos--on VLOOKUP and how to use it. https://exceljet.net/excel-functions/excel-vlookup-function