Hi,
I've tried a lot to adjust it, and finally, I did it in a table correctly.
The original data is in Table1.
The key was using the Defined Names approach, with INDIRECT, SUBSTITUTE, and ARRAYS.
The 1st problem I've got was that too many "-" are associated with many items.
To get rid of the "-", I've copied the contents of Table1 to Table2 using the following formula in F11.
1.png
Then, I collected each column with its next column to create an array for each item as follows:
Now, the first step is to create the arrays for the columns:
Main .... Selected K10:K11, and click this: Formulas > Defined Names > Create from Selection > ticked Top Row.
Type .... Selected K21:K22, and click this: Formulas > Defined Names > Create from Selection > ticked Top Row.
Sub .... Selected K46:K47, and click this: Formulas > Defined Names > Create from Selection > ticked Top Row.
These three steps created the desired defined name ranges to be used as arrays later in formulas (I will add the "#" when I need it as an array).
2.png
Now, the second step is to create the arrays for the different contents in each column:
Select K11:L18, and click this: Formulas > Defined Names > Create from Selection > ticked Left Column.
Select K22:L43, and click this: Formulas > Defined Names > Create from Selection > ticked Left Column.
Select K47:L70, and click this: Formulas > Defined Names > Create from Selection > ticked Left Column.
3.png
Again, These three steps created the desired defined name ranges to be used as arrays later in formulas (I will add the "#" when I need it as an array).
Now ... we move to the drop-down list table, Table3.
Column F, which is Main, will include the following in the Data Validation Source:
Column G, which is Type, will consist of the following in the Data Validation Source:
Column H, which is Sub, will include the following in the Data Validation Source:
Column H, which is Company, will include the following in the Data Validation Source:
If you reached this line, thanks for your time helping me.
Now, what I do hope I can find a solution for are:
1. How do you restore the "-" to the selections of the drop-down list? Is it possible to use something like the SUBSTITUTE to replace the result that contains the "No" with "-"?
2. After working with the drop list table, I found that if I changed the 1st column, the rest still existed. If I changed one by one, it was still, to some extent,t not adjusted, so I want to use the conditional format to highlight the other cells if anything is incorrect. I could only do the conditional format between 2 columns next to each other using the formula if the result in conditional format <> the indirect formula result.
But this is not what I am looking for, as I was wondering if any cell in a row in the table contains any item that is not associated with the rest of the items in the same row to have a highlighted entire row.
I thought about using something like XLOOKUP and linking it to the other arrays in the row, but I could not adjust it and kept getting an error.
For example, if you choose an item from 1st column and do the same with the other columns, this is correct, but if you change only the item in the first column, then the 2nd item will have an alert, but the third and fourth items will not be wrong because the second item is still correct from the aspect of third and fourth items (I hope that I did not lose you)
The bottom line is that unless all cells in the entire row are associated with each other and correct, I want the whole row to be highlighted in red.
4.png
Bookmarks