Nested IF not working - Please help - thx

dab1477

Board Regular
Joined
Jul 30, 2002
Messages
65
I J K L M (NOPQ) R S T U V W X Y
Ordered Product SKU Ordered Comm Completed Pack Single Potato PO# Packaging Comments Bags/min Production Time
Quantity Description Netweight Variety Order (x) on Line Units

800 10/5 lb. Ahold Russet USA (40034) R400 40000 POT RUS 3 8000 39 205
200 10/5 lb. Ahold Red USA (40015) RED284 10000 POT RED 2 2000 39 51
400 5/10 lb. GV Russet USA (279636) R442 20000 POT RUS 5 2000 30 67
200 10/5 lb. Ahold White USA (40002) W347 10000 POT WHT 5 2000 30 67
2 Per Pound White Fresh Peeled Whole A Size . . FF111 2 FCP WHT
54 Fingerlings . B Size 12/1.5lb Steam Carton . Side Delights
Steamables Steamable US #1 . F186 972 POT FIN 7 648 58 11
54 Medley RED/YELLOW/PURPLE C Size 12/1.5lb Steam Carton .
Side Delights Steamables Poly Roll US #1 . MDLY114 972 POT MDLY 6 #VALUE!
12 Mix . C Size 2 Var* 6Packs/ 1lb Side Delight Roastable Carton
Misc. Side Delight Roastable Roastable Tray US #1 . MIX153 144 POT MIX 5 #VALUE!

I placed column labels above headers to add clarity to explanation
The table is the construction of a rudamentary production schedule and also defines line load (I did not show the latter as it is not tied to the formulas).

The following formulas are in above excel table:
Column T: =IF(ISNUMBER(SEARCH("*POT*",M2)),SUM(I2*LEFT(TEXT(J2,"???/???"),FIND("/",TEXT(J2,"???/???"))-1)+0)," ")
Column X: =IF(S2>0,IFERROR(VLOOKUP(J2,ProductArray,10,FALSE)," ")," ")
Column Y: =IFERROR(IF(X2>0,SUM(T2/X2)," ")," ")
(NOPQ) are BLANK columns. No specific reason. This is due to the export of the table from a database. These are HIDDEN in the Excel sheet.
All other columns or data are NOT impacted by any formula, but are required for other non-formula issues or data entry.

Issue:
I am able to get columns X and Y to populate. They use a VLOOKUP table and then calculate. All is good there!
Column T is my issue. Column T works well until there are mulitple "/" in column J. I want the formula to find the NUMERATOR from within the description, then multiply that numerator by column I thus calculating a whole number. For instance: In the first line of the table above, I am able to successfully calculate finding the numerator from 10/5 lb. as 10 and then multiply by the 800 noted column I and return the value 8000 in column T.
When I attempt to do the same where column J has mulitple examples of "/", some used as separators, and one used as Numerator. An example where the statement does NOT work is the error #value! in column T. In this case, the file is seeing mulitple "/" in the description "Medley RED/YELLOW/PURPLE C Size 12/1.5lb Steam Carton .Side Delights Steamables Poly Roll US #1". I want the formula to pull the numerator from 12/1.5 lb and return 144 (i.e 12 * 54= 144) and ignore the separators in "Medley RED/YELLOW/PURPLE".

How do I single out ONLY the numerator and not the separators within the formula noted in column T?

Second issue is similar. How do I separate the number from the product description "Mix . C Size 2 Var* 6Packs/ 1lb Side Delight Roastable Carton Misc. Side Delight Roastable Roastable Tray US #1". Instead of the #value error noted on the last line of the above table, I want column T to cull the 6 from "6 packs/1 lb" and multiply that by column I, in this example...12; so that Column T would return 72 (i.e. 6*12 = 72). The issue here is the text immediately before "/".

I hope this makes sense. I can make it work when there is ONLY a numerator in the description, but am challenged when separators AND numerator exist within the description. Thanks in advance for the assist. I need this to be formula and not SQL or code. Ultimately this will upload to Google Sheets for mulitple department, real time, updates for any column currently blank.
 
Last edited:

dab1477

Board Regular
Joined
Jul 30, 2002
Messages
65
Table lost clarity upon posting. I believe and hope my question is still understandable. Sorry. Not sure how to correct.
 

AliGW

.
Joined
Mar 9, 2014
Messages
3,628
Grab the Forum Tools add-in for Excel (link in my signature line) and repost your table using that.
 

Forum statistics

Threads
1,082,139
Messages
5,363,362
Members
400,730
Latest member
cookie123

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top