Pulling rows that map to a specific criteria

[dostrob]

Hi all! I can use some help in creating a macro to pull specific rows that match a specific criteria and auto-populate/update

I will try to attach an example of what I'm thinking (if it attaches properly - sorry, new to Excel Forum)

1. 'Data' tab = The user would go in an specify if something is relevant "Yes" or "No" in column D
2. 'Risk' tab = Based on those classified as "Yes", there is a macro currently in the 'Risk' tab to pull in only those noted as "Yes". I think this works fine right now.
3. 'Control' tab (where I need some help) = I'm trying to pull all risk to control mappings based on the data in the 'risk-control map' tab. For example, if the 'Risk' tab shows that AC_1 is relevant (noted as "Yes" in the Data tab), then I am trying to have the 'Control' tab automatically pull all row data mapped to "AC_1" from the 'risk - control map' tab into this 'Control' tab. This would then be replicated to any other Risk ID's (AC_2, AC_5, AC_6, etc) that were defined with that same "Yes" in the 'Data' tab and shown in the 'Risk' tab. In the case of my excel example, we would have rows 2 - 9 and 19 - 27 from the 'risk-control map" tab be populated in the 'Control' tab since AC_1, AC_2, AC_5, and AC_6 were marked with a "yes" in the 'Data' tab and populated in the 'Risk' tab.

Hopefully this makes sense and someone can please assist?

Thank you, all!

[Mumps1]

Try:

Please Login or Register  to view this content.

[dostrob]

Thanks so much Mumps1 - this seems to work well.

Another question that comes up now is if there are manual entries in the cells to the right of each row in the 'Control' tab, every refresh of the tab would remove that manual data. I understand you can probably take off that clearcontents code or limit it to a specific cell range, but then any modification in the 'Data' tab will adjust the order / population of data and skew the manual input data and misalign it to the wrong rows.

Is there any known workaround to this?

Thank you!

[Mumps1]

You are very welcome.

The "clearcontents code" code doesn't delete any data in the Control sheet. I'm not sure what you mean by

any modification in the 'Data' tab will adjust the order / population of data and skew the manual input data and misalign it to the wrong rows.

Can you attach a version of your file which would clearly show the situation you are describing?

[dostrob]

So what I am trying to do is have the 'risk-control map' tab only show (either through an autofilter or hiding rows) what is selected in the 'Risk' tab. So if AC_4 and AC_6 are showing in the 'Risk' tab, then all rows pertaining to AC-4 and AC_6 are shown in the 'risk-control map' tab.

I also have a separate post to see if there is an autofilter solution that might be easier.

See attached.

Attachment 635378


Thank!

[Mumps1]

If I'm not mistaken, the Control sheet already will show what is selected in the 'Risk' tab. Are you saying that you want to eliminate the Control sheet and have the macro filter the risk-control map sheet instead?

[dostrob]

That is correct. Remove the Control sheet since I cant think of a way to then work on that sheet to input manual data without having it be lost with each refresh of the tab.

If we stay with the risk-control map sheet, then I see a potential to simply apply a filter and allow the user to manually put in more data in any subsequent column fields and not risk it being lost.

[Mumps1]

Try:

Please Login or Register  to view this content.

You can delete the Control sheet.

[dostrob]

Awesome! thanks so much works great... this was the final code i used to make it run on the risk-control map tab:

Please Login or Register  to view this content.

[Mumps1]

You are very welcome. Please edit your response and place code tags around your code. Highlight the code by selecting it and then press the # symbol in the menu at top.