Breakout numbers from a string and use/store each number as a variable

[mmmaxmmm]

So I've got a bunch of cells going down column A with strings like the below examples and I would like to be able to use each number in an IF function. Is there a way to store each number in A1 as a variable that I can use in an IF function and then once all the numbers are stored and used, move on to the next cell A2 and do the same thing? So 1 would be stored as a variable, used in an IF function. Then 25 would be stored as a variable, used in an IF function, then 50. Then it would reset or something, move on to A2 and start looping it like that through the end of column A. If anyone can post code, the IF function could just be the most simplest thing you can think of. My issue here is all the other stuff, not the IF function. Thank you to anyone who may be able to help!




A1 might have this as text: 1CA/25EA/50
A2 might have this as text: 1CA/100EA/5000PR/1000

 

[Rick Rothstein]

So I've got a bunch of cells going down column A with strings like the below examples and I would like to be able to use each number in an IF function. Is there a way to store each number in A1 as a variable that I can use in an IF function and then once all the numbers are stored and used, move on to the next cell A2 and do the same thing? So 1 would be stored as a variable, used in an IF function. Then 25 would be stored as a variable, used in an IF function, then 50. Then it would reset or something, move on to A2 and start looping it like that through the end of column A. If anyone can post code, the IF function could just be the most simplest thing you can think of. My issue here is all the other stuff, not the IF function. Thank you to anyone who may be able to help!

A1 might have this as text: 1CA/25EA/50
A2 might have this as text: 1CA/100EA/5000PR/1000

Do you need to know which row each number came from in your calculations or would the number processed as one long list, one listed value after another without regard what row they originally came from (for example using your posted data, 1, 25, 50, 1, 100, 5000, 1000, etc.).

 

[mmmaxmmm]

Do you need to know which row each number came from in your calculations or would the number processed as one long list, one listed value after another without regard what row they originally came from (for example using your posted data, 1, 25, 50, 1, 100, 5000, 1000, etc.).

Thanks Rick! Ideally it would "reset" after each row's numbers are used. So yes I would need to know which row each number came from.

 

[Peter_SSs]

Is this something you could work with?

Sub GetNumbers()
  Dim Nums As Object
  Dim data As Variant
  Dim r As Long, j As Long, num As Long
  
  data = Range("A1", Range("A" & Rows.Count).End(xlUp)).Value
  With CreateObject("VBScript.RegExp")
    .Global = True
    .Pattern = "\d+"
    For r = 1 To UBound(data)
      Set Nums = .Execute(data(r, 1))
        For j = 1 To Nums.Count
          num = Nums.Item(j - 1)
          'Do whatever you want here
          'The number is stored in the variable 'num'
          'The row it comes from is stored in the variable 'r'
        Next j
    Next r
  End With
End Sub

 

[mmmaxmmm]

Is this something you could work with?

Sub GetNumbers()
  Dim Nums As Object
  Dim data As Variant
  Dim r As Long, j As Long, num As Long
  
  data = Range("A1", Range("A" & Rows.Count).End(xlUp)).Value
  With CreateObject("VBScript.RegExp")
    .Global = True
    .Pattern = "\d+"
    For r = 1 To UBound(data)
      Set Nums = .Execute(data(r, 1))
        For j = 1 To Nums.Count
          num = Nums.Item(j - 1)
          'Do whatever you want here
          'The number is stored in the variable 'num'
          'The row it comes from is stored in the variable 'r'
        Next j
    Next r
  End With
End Sub

Peter thank you. This code is a bit beyond my skill level so please give me a little time to understand it and try and work it into what I'm doing. If I run into a wall, I'll reply here in the next few days. From what I can understand, this is getting pretty close to what I'm trying to do!

 

[mmmaxmmm]

OK I tried to make it work on my own but it's beyond my skill level. What I'd like to do next is, now that we've got the first number in that string set as "num"...

I'd like to place it in column B on that same row and then check whether or not column C (still same row) is between .75 and -.75.
If C is between .75 and -.75, then move on to the next row.
However, If column C is still not between those two numbers, try placing the next "num" in the string that's in column A into column B and checking if column C is between .75 and -.75.
If still not between those two numbers, then try the next "num" so forth and so on until there are no more "num"s in that row.
If there are no more "num"s in that row then move on to the next row.

And of course at any point if putting "num" into column B does cause column C to be between those two numbers, then leave it there and highlight it yellow and move on to the next row.

Gosh I hope that makes sense! Thank you to anyone that can help.

 

[Peter_SSs]

Since we are not familiar with your data, perhaps you could post some sample data (say 8-10 rows) and include the expected results with any further explanation?
There's a link in my signature block below for help with how to do that.

How does placing a different number in column B change what is in column C? Is there a formula in column C that refers to column B? If so, it would be good to know what that formula is too.

 

[mmmaxmmm]

Since we are not familiar with your data, perhaps you could post some sample data (say 8-10 rows) and include the expected results with any further explanation?
There's a link in my signature block below for help with how to do that.

How does placing a different number in column B change what is in column C? Is there a formula in column C that refers to column B? If so, it would be good to know what that formula is too.

Wow this is a pretty neat tool. Hopefully this makes more sense now that you see the data. I've hidden some columns that don't pertain to this. And I'm sorry I was using hypothetical columns before, below are the actual columns where the data sits. So that Variance column in AU...that has a formula in it that takes the percent difference between the two prices (AE and AR). Column Z is where I'd like to insert each number in the "Packaging String" (AC) and then depending on the result in Column AU, move on to the next row. As I mentioned earlier, taking the numbers in the packing string and testing whether or not they bring the variance to between -.75 and .75 is my goal. If the number in the packaging string is successful in bringing the variance in AU to between -.75 and .75, then move on to the next row and leave whatever number that was successful in column Z and highlight it so I know it was changed. If none of the packaging string numbers bring the variance (AU) to within the -.75 and .75, then leave the initial number in Z.

I realize it's a complex set of logical steps. I tried on my own but this is well beyond me. Thank you so much for any help you may provide.


Excel 2012

	Q	Y	Z	AC	AD	AE	AF	AG	AH	AQ	AR	AU
31247003A	PK	1SP/3EA/3	$ 4.63	$ 1.54
M-00-S/50	PK	1PK/50EA/N/A	$ -	$ -
M-00-S/50	PK	1PK/50EA/N/A	$ -	$ -
VLC-00-S/25	PK	1PK/25EA/N/A	$ 72.82	$ 0.07
VLC-00-S/25	EA	1PK/25EA/N/A	$ 72.82	$ 0.07
VLC-00-S/10	EA	1PK/10EA/N/A	$ 72.82	$ 0.07
VLC-00-S/10	PK	1PK/10EA/N/A	$ 72.82	$ 0.07
VLC-00-S/10	PK	1PK/10EA/N/A	$ 72.82	$ 0.07
SU-00-C/100	PK	1PK/100EA/N/A	$ 71.46	$ 0.02
D2411+10	EA	1EA/N/A	$ 15.79	$ 1.58

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]6[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=612141]#612141[/URL] , align: center"]Catalog Num[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=612141]#612141[/URL] , align: center"]Pkg UOM[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=612141]#612141[/URL] , align: center"]UOM Conv[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=612141]#612141[/URL] , align: center"]Packaging String[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=612141]#612141[/URL] , align: center"] Facility Base Price [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=612141]#612141[/URL] , align: center"] Last Base Each Price [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=612141]#612141[/URL] , align: center"]Annual QTY[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=612141]#612141[/URL] , align: center"]Annual Eaches[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=612141]#612141[/URL] , align: center"] Annual Spend [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] , align: center"]Mapped Pkg Price[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] , align: center"]Mapped EA Price[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] , align: center"]Variance[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]3[/TD]

[TD="align: right"] $ 5.62 [/TD]
[TD="align: right"] $ 1.87 [/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"] $ 22.48 [/TD]

[TD="align: center"]18%[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]50[/TD]

[TD="align: right"] $ 13.75 [/TD]
[TD="align: right"] $ 0.28 [/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]2000[/TD]
[TD="align: right"] $ 550.00 [/TD]

[TD="align: center"]0%[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]50[/TD]

[TD="align: right"] $ 13.75 [/TD]
[TD="align: right"] $ 0.28 [/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]400[/TD]
[TD="align: right"] $ 110.00 [/TD]

[TD="align: center"]0%[/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]25[/TD]

[TD="align: right"] $ 13.21 [/TD]
[TD="align: right"] $ 0.53 [/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"] $ 52.84 [/TD]

[TD="align: center"]86%[/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]1[/TD]

[TD="align: right"] $ 7.64 [/TD]
[TD="align: right"] $ 7.64 [/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"] $ 30.58 [/TD]

[TD="align: center"]99%[/TD]

[TD="align: center"]12[/TD]

[TD="align: right"]1[/TD]

[TD="align: right"] $ 3.08 [/TD]
[TD="align: right"] $ 3.08 [/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"] $ 30.80 [/TD]

[TD="align: center"]98%[/TD]

[TD="align: center"]13[/TD]

[TD="align: right"]10[/TD]

[TD="align: right"] $ 3.08 [/TD]
[TD="align: right"] $ 0.31 [/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"] $ 154.00 [/TD]

[TD="align: center"]76%[/TD]

[TD="align: center"]14[/TD]

[TD="align: right"]10[/TD]

[TD="align: right"] $ 5.53 [/TD]
[TD="align: right"] $ 0.55 [/TD]
[TD="align: right"]72[/TD]
[TD="align: right"]720[/TD]
[TD="align: right"] $ 398.16 [/TD]

[TD="align: center"]87%[/TD]

[TD="align: center"]15[/TD]

[TD="align: right"]100[/TD]

[TD="align: right"] $ 23.78 [/TD]
[TD="align: right"] $ 0.24 [/TD]
[TD="align: right"]72[/TD]
[TD="align: right"]7200[/TD]
[TD="align: right"] $ 1,712.16 [/TD]

[TD="align: center"]92%[/TD]

[TD="align: center"]16[/TD]

[TD="align: right"]1[/TD]

[TD="align: right"] $ 17.50 [/TD]
[TD="align: right"] $ 17.50 [/TD]
[TD="align: right"]102[/TD]
[TD="align: right"]102[/TD]
[TD="align: right"] $ 1,785.00 [/TD]

[TD="align: center"]91%[/TD]

</tbody>

Detail​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]AE7[/TH]
[TD="align: left"]=AD7/Z7[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]AE8[/TH]
[TD="align: left"]=AD8/Z8[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]AE9[/TH]
[TD="align: left"]=AD9/Z9[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]AE10[/TH]
[TD="align: left"]=AD10/Z10[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]AE11[/TH]
[TD="align: left"]=AD11/Z11[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]AE12[/TH]
[TD="align: left"]=AD12/Z12[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]AE13[/TH]
[TD="align: left"]=AD13/Z13[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]AE14[/TH]
[TD="align: left"]=AD14/Z14[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]AE15[/TH]
[TD="align: left"]=AD15/Z15[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]AE16[/TH]
[TD="align: left"]=AD16/Z16[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]AG7[/TH]
[TD="align: left"]=AF7*Z7[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]AG8[/TH]
[TD="align: left"]=AF8*Z8[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]AG9[/TH]
[TD="align: left"]=AF9*Z9[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]AG10[/TH]
[TD="align: left"]=AF10*Z10[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]AG11[/TH]
[TD="align: left"]=AF11*Z11[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]AG12[/TH]
[TD="align: left"]=AF12*Z12[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]AG13[/TH]
[TD="align: left"]=AF13*Z13[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]AG14[/TH]
[TD="align: left"]=AF14*Z14[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]AG15[/TH]
[TD="align: left"]=AF15*Z15[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]AG16[/TH]
[TD="align: left"]=AF16*Z16[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]AQ7[/TH]
[TD="align: left"]=IFERROR(INDEX('New Price List'!$B$11:$W$50000,MATCH($AM7,'New Price List'!$A$11:$A$50000,0),MATCH(AQ$5,'New Price List'!$B$10:$W$10,0)),0)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]AR7[/TH]
[TD="align: left"]=IFERROR(AQ7/$AP7,0)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]AQ8[/TH]
[TD="align: left"]=IFERROR(INDEX('New Price List'!$B$11:$W$50000,MATCH($AM8,'New Price List'!$A$11:$A$50000,0),MATCH(AQ$5,'New Price List'!$B$10:$W$10,0)),0)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]AR8[/TH]
[TD="align: left"]=IFERROR(AQ8/$AP8,0)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]AQ9[/TH]
[TD="align: left"]=IFERROR(INDEX('New Price List'!$B$11:$W$50000,MATCH($AM9,'New Price List'!$A$11:$A$50000,0),MATCH(AQ$5,'New Price List'!$B$10:$W$10,0)),0)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]AR9[/TH]
[TD="align: left"]=IFERROR(AQ9/$AP9,0)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]AQ10[/TH]
[TD="align: left"]=IFERROR(INDEX('New Price List'!$B$11:$W$50000,MATCH($AM10,'New Price List'!$A$11:$A$50000,0),MATCH(AQ$5,'New Price List'!$B$10:$W$10,0)),0)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]AR10[/TH]
[TD="align: left"]=IFERROR(AQ10/$AP10,0)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]AQ11[/TH]
[TD="align: left"]=IFERROR(INDEX('New Price List'!$B$11:$W$50000,MATCH($AM11,'New Price List'!$A$11:$A$50000,0),MATCH(AQ$5,'New Price List'!$B$10:$W$10,0)),0)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]AR11[/TH]
[TD="align: left"]=IFERROR(AQ11/$AP11,0)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]AQ12[/TH]
[TD="align: left"]=IFERROR(INDEX('New Price List'!$B$11:$W$50000,MATCH($AM12,'New Price List'!$A$11:$A$50000,0),MATCH(AQ$5,'New Price List'!$B$10:$W$10,0)),0)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]AR12[/TH]
[TD="align: left"]=IFERROR(AQ12/$AP12,0)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]AQ13[/TH]
[TD="align: left"]=IFERROR(INDEX('New Price List'!$B$11:$W$50000,MATCH($AM13,'New Price List'!$A$11:$A$50000,0),MATCH(AQ$5,'New Price List'!$B$10:$W$10,0)),0)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]AR13[/TH]
[TD="align: left"]=IFERROR(AQ13/$AP13,0)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]AQ14[/TH]
[TD="align: left"]=IFERROR(INDEX('New Price List'!$B$11:$W$50000,MATCH($AM14,'New Price List'!$A$11:$A$50000,0),MATCH(AQ$5,'New Price List'!$B$10:$W$10,0)),0)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]AR14[/TH]
[TD="align: left"]=IFERROR(AQ14/$AP14,0)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]AQ15[/TH]
[TD="align: left"]=IFERROR(INDEX('New Price List'!$B$11:$W$50000,MATCH($AM15,'New Price List'!$A$11:$A$50000,0),MATCH(AQ$5,'New Price List'!$B$10:$W$10,0)),0)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]AR15[/TH]
[TD="align: left"]=IFERROR(AQ15/$AP15,0)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]AQ16[/TH]
[TD="align: left"]=IFERROR(INDEX('New Price List'!$B$11:$W$50000,MATCH($AM16,'New Price List'!$A$11:$A$50000,0),MATCH(AQ$5,'New Price List'!$B$10:$W$10,0)),0)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]AR16[/TH]
[TD="align: left"]=IFERROR(AQ16/$AP16,0)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]AU7[/TH]
[TD="align: left"]=IF(AR7=0,0,($AE7-AR7)/$AE7)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]AU8[/TH]
[TD="align: left"]=IF(AR8=0,0,($AE8-AR8)/$AE8)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]AU9[/TH]
[TD="align: left"]=IF(AR9=0,0,($AE9-AR9)/$AE9)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]AU10[/TH]
[TD="align: left"]=IF(AR10=0,0,($AE10-AR10)/$AE10)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]AU11[/TH]
[TD="align: left"]=IF(AR11=0,0,($AE11-AR11)/$AE11)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]AU12[/TH]
[TD="align: left"]=IF(AR12=0,0,($AE12-AR12)/$AE12)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]AU13[/TH]
[TD="align: left"]=IF(AR13=0,0,($AE13-AR13)/$AE13)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]AU14[/TH]
[TD="align: left"]=IF(AR14=0,0,($AE14-AR14)/$AE14)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]AU15[/TH]
[TD="align: left"]=IF(AR15=0,0,($AE15-AR15)/$AE15)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]AU16[/TH]
[TD="align: left"]=IF(AR16=0,0,($AE16-AR16)/$AE16)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Defined Names[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Name[/TH]
[TH="align: left"]Refers To[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]Detail!a[/TH]
[TD="align: left"]=#REF!#REF![/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]Detail!B[/TH]
[TD="align: left"]=#REF!#REF![/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]Detail!D[/TH]
[TD="align: left"]=#REF!#REF![/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]Detail!E[/TH]
[TD="align: left"]=#REF!#REF![/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[mmmaxmmm]

Hi folks, are there any takers on this one? I realize it's not an easy one. But if there's any suggestions on where I can look to find the answer or if anyone knows of any pre-existing code that might help, I can tweak it myself possibly. Anything helps!

 

[Peter_SSs]

I will look at it but it mightn't have sufficient time for a day or two.