Need to define dynamic range for column I to K based on dynamic column A

[flabb]

I'm pretty new to VBA, so please be patient with me.

I need to compare values in column I to K for the item (here: numbers) in column A. The different items in column A har different amount of properties, so for example;
Item 12 has 30 properties, item 13 har 8 properties, item 14 has 20 properties.
Along with the properties there are values (represented in column I to T). These values needs to be compared to a constant number (f.ex. all values higher than 100-> red color).
BUT there are different criterias for the different items, f.ex; values for item 12A(I to K) gets red it alue is higher than 100, values for item 12B(L to N) gets red if value is higher than 500, value for item 13A(I to K) gets red if value is higher than 1000, and so on.

The attachment shows 4 items (12 to 15), but this could be 100 items or 15 items, this list is variable (along with the properties to each item).

How do I do this?
Please see attachment to better understand the question .

Thanks!

[Bernie Deitrick]

You don't need VBA, but your do need to create a value table from which you can extract the limit for each combination of number and letter, as in this example, which uses a table in $V$9:$Z$13 and the CF formula (starting in I9)

=I9>=INDEX($V$9:$Z$13,MATCH(MAX($A$9:$A9),$V$9:$V$13,FALSE),INT((COLUMN(I9)-9)/3)+2)



Test CF example.xlsx

[flabb]

Hi.
Thanks, but this does not work if the number of properties (and therefore number of rows) under each item is changed/dynamic. I am supposed to delete tha whole data area (form A9 to T97 in this case) and replace it with datas for exaple from A9 to T400, and then the first item might consist of only 3 properties, or 20, or 45 (It can vary). And I want to just copy-paste the datas, and not have to set ut CF codes every time I get new datas.
Thanks for helping anyway!

[Bernie Deitrick]

The CF is dynamic. Instead of just pasting and wiping out the formatting, simply paste values starting in column I (A through H can be a simple paste), and then copy the formatting of I9 down and over to match whatever range you have, and the CF will work with the entire range. You just have to have a big enough rules table. The one requirement, which appears to have been met by what you posted, is that the numbers in column A be in ascending order.

Bernie

[flabb]

Hi.
Thanks, but I don't think this works. This workbook is a part of a macro creating the datas. So the numbers will be deleted and replaced with other numbers. If the CF codes refers to a range with 30 rows, and the new item after deletion/replacing bar 12 properties/rows, the rest of the rows (18 rows) will still be matched with the referenced number for the first item (and not the second as they should be), wouldn't they?
This have to be adjusted automatically.
This is also a smaller part in a "big plan"; to get the actual property of the number above a certain value inserted in another sheet (along with the number).

[dflak]

How is this report generated? It looks like it is the result of a pivot table. At the moment, I am not sure if being able to see the source data would help or not. In any event, you will need a table off to the side to set the rules with Number and Letter and break point such as 12A:100, 12B:500, 12C:1000, etc. Can you provide us with such a table?

[Bernie Deitrick]

the rest of the rows (18 rows) will still be matched with the referenced number for the first item (and not the second as they should be), wouldn't they?

No. The formula that finds the reference number is this part

MAX($A$9:$A9)

As the CF formula is copied down, that part changes to


MAX($A$9:$A10)

MAX($A$9:$A11)

etc.

So the reference number stays the value in A9 until another number is found, at which time it changes. That is why I said that the formula depends on the values in A being in ascending order: so that the MAX will work.

Rather than saying "This doesn't work" without trying it, you should, rather, trust that the responses you get here will work - they may not fit exactly what you have, because only some of your requirements are actually communicated, but they will usually work and work really well. There are a lot of tricks and techniques that make Excel work easier and better, and the folks here (including me) have years and years of experience doing spreadsheets. I constantly learn neat things from reading the posts here - I started doing data analysis in Lotus 123 at least 30 years ago, so I have picked up a lot of knowledge along the way.