Hi,
I have a very complex spreadsheet with 600+ records spanning about 1900 rows (some rows have merged cells). In the first column, there is a security control number (alpha-numeric according to NIST SP 800-53). In another column, there is a corresponding cell that is a Y / ? / N/A field, requiring user validation as to whether the control is applicable to the system they are building (Y's are applicable, N/A's are not, and ?'s need to be changed to Y or N/A).
For those not familiar with NIST 800-53, it is the Federal standard for security controls on information systems, and is divided into 17 different "families" or relevancy groups (access control, audit, training, etc.).
What I would like to do is based on all the Y's in the applicability column, build a table or summary of the applicable controls, but divided into the appropriate families (AC controls listed in one column, AT in the next, etc.). This way, system builders would have a summarized list of only the controls that are valid for their system, without having to build it out manually (possibly missing some because there are so many).
So, my primary question is how can I report the values from a cell on one worksheet into another worksheet, but based off the value of a related cell in the 1st sheet? Any input would be greatly appreciated.
Many thanks, in advance.
Joe
I have a very complex spreadsheet with 600+ records spanning about 1900 rows (some rows have merged cells). In the first column, there is a security control number (alpha-numeric according to NIST SP 800-53). In another column, there is a corresponding cell that is a Y / ? / N/A field, requiring user validation as to whether the control is applicable to the system they are building (Y's are applicable, N/A's are not, and ?'s need to be changed to Y or N/A).
For those not familiar with NIST 800-53, it is the Federal standard for security controls on information systems, and is divided into 17 different "families" or relevancy groups (access control, audit, training, etc.).
What I would like to do is based on all the Y's in the applicability column, build a table or summary of the applicable controls, but divided into the appropriate families (AC controls listed in one column, AT in the next, etc.). This way, system builders would have a summarized list of only the controls that are valid for their system, without having to build it out manually (possibly missing some because there are so many).
So, my primary question is how can I report the values from a cell on one worksheet into another worksheet, but based off the value of a related cell in the 1st sheet? Any input would be greatly appreciated.
Many thanks, in advance.
Joe
