JonathanRSwift
New Member
- Joined
- May 29, 2019
- Messages
- 6
On Sheet 1, I have a master list of data, with corresponding attributes. Some data has more than one attribute, some has only one, and there is a possibility of blanks. Attributes can be repeated when assigned to a different name.
I've posted some example data below, so we can all talk about the same cells/names etc.
On Sheet 2, there is much more freeform data input/analysis going on. Users are able to select a 'Name' from a dropdown menu using Data Validation, and are then able to select from the available attributes corresponding to that name, again using a dropdown menu. Names and attributes can appear in any order on Sheet 2.
It is important that all pairings are considered in the second worksheet.
Is it possible to use conditional formatting to highlight the 'Name' field (on Sheet 2) until at least one row exists with all possible pairings? In the example below, you can see that we have forgotten to put any info relating to the fact that Sally is Happy, and consequently 'Sally' has been highlighted to draw attention to the fact that there is some missing information.
Current thoughts: I already have a list of the attributes that match the corresponding name- this is what drives the dropdown menu on Sheet 2, and is generated in a background sheet when a name is picked on Sheet 2. I can count the non-blank cells in this range to find out the total number of pairs that are required.
I would like to then count the number of non-duplicate attributes that are on rows that have the same Name as the current row, and compare this value.
I'm expecting this to get into the realms of array formulae, but may be wrong... I'm also expecting array formulae to not work directly with conditional formatting, and to require the use of a 'helper column' to drive the formatting. Let me know if this is incorrect?
Something along the lines of the below (formatted as pseudo-code for readability, but this should be read as a high level description, not actual code)
<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">{Count the 1s in the array(AND(
'Check if it's a name match'
If($D$1:$D$10=[$ACurrent],[set flag to 1],[set flag to 0])
'Check if it's a unique value'
[somehow check array values set at 1 to see if there is a duplicate value in column E, and then set the array value to zero if so])
}</code>Does this approach make sense, and how would I go about constructing this actual formula?
I don't mind using VBA if required, but would prefer to avoid it if possible (company policy, sorry).
I've posted some example data below, so we can all talk about the same cells/names etc.
On Sheet 2, there is much more freeform data input/analysis going on. Users are able to select a 'Name' from a dropdown menu using Data Validation, and are then able to select from the available attributes corresponding to that name, again using a dropdown menu. Names and attributes can appear in any order on Sheet 2.
It is important that all pairings are considered in the second worksheet.
Is it possible to use conditional formatting to highlight the 'Name' field (on Sheet 2) until at least one row exists with all possible pairings? In the example below, you can see that we have forgotten to put any info relating to the fact that Sally is Happy, and consequently 'Sally' has been highlighted to draw attention to the fact that there is some missing information.
Current thoughts: I already have a list of the attributes that match the corresponding name- this is what drives the dropdown menu on Sheet 2, and is generated in a background sheet when a name is picked on Sheet 2. I can count the non-blank cells in this range to find out the total number of pairs that are required.
I would like to then count the number of non-duplicate attributes that are on rows that have the same Name as the current row, and compare this value.
I'm expecting this to get into the realms of array formulae, but may be wrong... I'm also expecting array formulae to not work directly with conditional formatting, and to require the use of a 'helper column' to drive the formatting. Let me know if this is incorrect?
Something along the lines of the below (formatted as pseudo-code for readability, but this should be read as a high level description, not actual code)
<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">{Count the 1s in the array(AND(
'Check if it's a name match'
If($D$1:$D$10=[$ACurrent],[set flag to 1],[set flag to 0])
'Check if it's a unique value'
[somehow check array values set at 1 to see if there is a duplicate value in column E, and then set the array value to zero if so])
}</code>Does this approach make sense, and how would I go about constructing this actual formula?
I don't mind using VBA if required, but would prefer to avoid it if possible (company policy, sorry).
