Dynamically change list values

[michaelburo]

I have data in a spreadsheet, called volunteers. In the last 5 columns are a series of Yes/No responses, such as "Call Voters", "Work the polls", "Fundraise" etc. What I would like to do is create individual sheets based on data culled from the main sheet (volunteers), so sheet 2 would consist only of those who said "yes" to call voters, sheet 2, "yes" to work the polls, etc. I can create it ONCE by using various methods but sheets 2 thru sheets x, do not automatically update based on changing values in volunteers, i.e, No to Yes or by the addition of new people. Is this possible?

 

[jarjarbingie]

I have data in a spreadsheet, called volunteers. In the last 5 columns are a series of Yes/No responses, such as "Call Voters", "Work the polls", "Fundraise" etc. What I would like to do is create individual sheets based on data culled from the main sheet (volunteers), so sheet 2 would consist only of those who said "yes" to call voters, sheet 2, "yes" to work the polls, etc. I can create it ONCE by using various methods but sheets 2 thru sheets x, do not automatically update based on changing values in volunteers, i.e, No to Yes or by the addition of new people. Is this possible?

I believe it is possible. Perhaps you can upload a sample file somewhere and provide the link here.

 

[Trebor76]

Hi michaelburo,

Welcome to MrExcel!!

In my humble opinion creating separate sheets from a main sheet like you're proposing is not the way to go. It is quite possible via a macro but why don't you just filter the columns on the main sheet to get the dataset you're after?

Robert

 

[michaelburo]

Let me know if this doesnt work
Sample.xlsx - Google Drive

 

[jarjarbingie]

Let me know if this doesnt work
Sample.xlsx - Google Drive

in Polling sheet, assuming you have the same headers with Raw Data sheet from A1 (Last Name) to I1 (Pref Contact Method):

A2 = IFERROR(INDEX(Rawdata!A$4:A$103,SMALL(IF(OFFSET(Rawdata!$I$3,1,MATCH("Polling Official",Rawdata!$J$3:$S$3,0),100,1)="X",ROW(Rawdata!$A$4:$A$103),""),ROW()-1)-3),"") press CTRL+SHIFT+ENTER

then drag the formulas to the right and then down..

 

[michaelburo]

It seems to work the way I wanted, either by adding or subtracting information from the rawdata sheet and then updating the corresponding named sheet. Now my other question is if I wanted to repeat the process, would all I have to do is simply change the name ("Polling Official") to whatever column ("Maintain Mail List") I wished to extract information from?

 

[jarjarbingie]

It seems to work the way I wanted, either by adding or subtracting information from the rawdata sheet and then updating the corresponding named sheet. Now my other question is if I wanted to repeat the process, would all I have to do is simply change the name ("Polling Official") to whatever column ("Maintain Mail List") I wished to extract information from?

yes, you can just copy the sheet and use CTRL+H to change the column name.. make sure it matches whatever you have in the raw data sheet..

 

[michaelburo]

thank you, thank you, thank you