Excel help needed-
What I'm trying to do is create a spreadsheet where as entries are added on the first sheet, there will be a "yes or no" drop down near each name. Any of the names where the user selects "yes" will then automatically populate into the second sheet, which will have similar columns, but not quite the same. The goal here is that sheet 1 will be a large sample of individuals, but only some of them will be identified as "more info needed", so they can get grouped into sheet 2.
Thing is, it has to be set up so that names can continually be added or removed, without having to copy formulas every time. Any ideas? Screenshot related- I can't post the actual document cuz it's for my job, but here's an example. Thanks a TON for any input.
>>97111
Fundamental principle of programming: DRY.
Don't. Repeat. Yourself.
Less succinctly, "every item of data should be defined in one, and only one, place".
What this means for Excel is that you should not be creating or updating copies of your data.
What you should be doing instead is keeping all your data on sheet 1, and inserting a pivot table into sheet 2 that /references/ sheet 1.
Google "introduction to pivot tables", because a pivot table does everything you need to solve your problem.
Also, if you're using Excel in your job and don't already know this, for heaven's sake read a book on Excel. There'll be a million things Excel could be doing to make your life easier, but you're not using them because you don't yet know they're possible.
>>97111
Also, check out Excel's data integrity constraints. If your column should only ever say "yes" or "no", you can apply that constraint to a column, and Excel will make it impossible to enter any other value.
It can also simplify data entry by turning every cell into a dropdown with only the valid options available.
>>97114
>>97111
>The goal here is that sheet 1 will be a large sample of individuals, but only some of them will be identified as "more info needed", so they can get grouped into sheet 2.
If you're planning on doing this, store the actual data on sheet 1, and hide the columns. DRY.
It's way easier to keep all your data in one place than to keep some kind of relational mapping between two sheets when you don't need to.
>>97119
Op here. Makes sense, but I won't be the one inputting the data moving forward. It's going to be someone else adding entries to sheet 1, and when they select "yes" for a name, it'll add an entry on the next sheet for them to work with. Once the work on that entry is complete, they can switch the box on sheet 1 back to "no", and it'll disappear from the rest of the workbook, but remain on sheet 1 for tracking purposes. I'd love to preload all the data into hidden columns and have it just appear when necessary, but unfortunately it's a growing list and I won't be touching it after the sheet is done. Just trying to articulate the goal here, I wish I could just post the actual file I have to work on, but it contains a lot of sensitive personal data on people.
>>97115
I'm familiar with this, and trust me, I'll be implementing wherever possible. Most fields will be restricted to only dates, choices from a list, etc. I essentially have to child-proof it, because when it's done, it'll be passed on to someone who is largely computer illiterate. I'm no expert with this stuff myself, but the project was given to me because I'm that younger guy in the office that sorta "knows computers."
>>97132
>it'll add an entry on the next sheet for them to work with. Once the work on that entry is complete, they can switch the box on sheet 1 back to "no", and it'll disappear from the rest of the workbook, but remain on sheet 1 for tracking purposes.
I think you can do this with pivottables, but you'd be better off with VBA.
Or, y'know, Access, seeing as you're trying to hammer a database peg through a spreadsheet hole.
>>97138
"database peg through a spreadsheet hole"
It's true. I should tell my boss that. He... won't understand at all. -_-
Thanks all for the suggestions. I'll start digging into the pivot table function and see if this is doable.
