Looking for a way to create a list based off of values entered by other users

Hello,

First and foremost, I am on Windows XP and using Excel 2003. I have some moderate level of understanding of basic Excel functions and formulae, but zero experience with VBA and macros.

All right, now that that's out of the way, let me start off by stating what I am trying to accomplish: I have a workbook with several sheets. Various users other than myself will be filling out drop-boxes that I have created on the sheets of the workbook. Based on the results of these users' input, I want to generate a list that pulls some data from the tables on the sheets these folks are working with and consolidate it on a separate sheet. I will not know how many rows will be needed, as this will change as the drop-boxes are altered. I also need this consolidated list to update while the other users are still working on their parts of the document.

Let's try to illustrate with an example:
-Sheet 1, 2 and 3 have the following (or similar) information:

|---------|-Front-|-Side-|-Rear-|
|-Aston-M-|-Pass--|-Pass-|-Fail-|
|-Lotus---|-Fail--|-Pass-|-Pass-|
|-Tesla---|-Pass--|-Pass-|-Pass-|
|-Ferrari-|-Pass--|-Fail-|-Fail-|


Each of the Pass/Fail fields is a drop-down list that I have defined, but the actual results will be reported by other users.

On Sheet 4, I would like to generate a list that looks through Sheets 1, 2 and 3, finds all of the "Fail" results and then creates a list on Sheet 4 that lists the contents of Column A (the name of the car), then the title of the column in which the "Fail" was reported so that they can put any notes related to the failure on Sheet 4. Here's an example of what I'd like to see *magically* appear on Sheet 4 based on the results from the example above, where column A and B are created automatically and the user can then fill out column C:

|- Aston-M -|- Rear- -|- Note: Test Dummy fatality on initial rear impact -|
|- Lotus-- -|- Front -|- Note: Test Dummy ejected from vehicle -|
|- Ferrari -|- Side- -|- Note: Test Dummy decapitation -|
|- Ferrari -|- Rear- -|- Note: Test Dummy spontaneous combustion upon impact -|

My hope is that this could create one consist list of all of the "Fail" conditions and allow room for additional notes to be taken.

I have been scouring Google looking for some guidance, but have yet to find any. I know that I could use VLOOKUP and HLOOKUP to grab the values that I'm looking for, but I believe with this approach, I would likely end up with a ton of blank cells or gaps in the list. I'm sure there has to be a better solution out there.

Any help would be greatly appreciated, and thanks in advance!

I can help you develop a solution if you can attach a workbook with real data (it can be a subset of your live data, but having actual fields is important). My approach would be to count the number of FAILs in each column and then, for each line in the user-entry worksheets, have a flag to indicate which ones FAILed. Then, in your summary tab, you can use lookups as you suggest. It does require a few "helper" columns, but those can be automated and hidden out of the way.

If this approach sounds reasonable, I'd be happy to help you design a solution.

Hello Bentleybob,

Thank you for your response. Unfortunately, I cannot attach the actual document due to Non-disclosure agreements, however, I have produced a document that has the same setup and formatting and fields (with text replaced with generic nonsense). It's really not a very complicated doc, so I hope that this will be enough.

Your solution - if I understand it correctly - actually sounds like it could work out very well. I think I see the shape of what you're trying to do, and I hope that seeing it in action will better my understanding.

Thanks again for helping out and taking a stab!

Well, it took a bit more helper material, but I stashed that away in Sheet4. The attached appears to meet your criteria, so please let me know how it works in the live version. Assuming the number of columns and rows of data (and that you are, in fact, using three sheets of data), setup shouldn't take very long. Once you've set up the tables, it should work automatically.

Hey, that looks just about perfect! Thanks Bentleybob. A bit later this afternoon, I'll try transposing it into the live doc and sees how it rolls :-D. Just one question for you; how might one go about unhiding those helper cells in Sheet 4? I'd love to take a look at exactly how you went about tackling this (and build upon my own understanding in the process).

Thanks again, this is a great help!

Oops... dupe posted...

I simply froze sheet panes (in Excel 2003, I believe you want Windows, Unfreeze Panes).

::Blush:: Ah... Yes... of course... :-)

Cool, thanks! Going to try and incorporate it in my real-life doc, it looks like it will work quite well!

Thanks again, I'll let you know how it turns out.

Thank you Bentleybob! Your solution worked fairly well.

I had to tweak things a bit, but it was my own fault because the example I gave you had less sheets than I wound up with. At some point, I hit the nesting cap for the IF statements looking up Column Lookups, but I just added a second column that broke the list of sheets in half and restarted the lookup string. I just set up the final FALSE in the lookup to return "ZZZ" and then in the second column told it to start the lookups again if it found "ZZZ" and to otherwise simply report the value of the first column.

Anyway, your sheet did everything I asked and I have learned a lot in the past couple of days. Now, I have encountered a new issue. The list will work as-is, however in an ideal world, I would love to keep the other users' notes attached to the same row as the variable. Since the list is dynamic, but any notes that someone adds to it is not, I foresee a possibility of getting the data mixed up if we're not careful (and I don't have faith in my users to be careful). I'll poke around and see if I can come up with any solutions of my own, but if you have any input, I'd appreciate it :-)

Thanks much for your help, you've been awesome!

Also, I should mention that they just upgraded my department to Excel 2007, so if there are any easier tricks in the new(er) version, that is an option too.

The sample you sent didn't include any notes, although I see in your original post that they somehow appear in Sheet4. I presume, however, that the source is really the other (input) sheets. Assuming this is correct, then using the same basic approach, it shouldn't be hard to pull the notes in as well.

DoI understand correctly that your concern is that the users may change or delete the notes at any time, at which point you've lost them?

Well, that might have changed the approach somewhat, but if your system works now I wouldn't mess with it!

Yeah, let me back track a step or two and clarify the "notes" part and the two ways I picture it happening:

1) The individual sheets with the pass/fail data currently has no convenient place to place bits of additional info collected during the test cycle. Ideally, after the user marks a cell in Sheet1 as "Fail", they then switch to Sheet4 which has the generated, dynamic list. On Sheet4, I have added an additional column for any specific information they might have to add to the list. In the first post on this thread, all of the color commentary about the Test Dummies would be entered directly in to Sheet4, after the list has been generated by all the marvelous Excel magic.

In this case, the challenge comes in when the user goes back and adds another "Fail" cell that precedes the one they already added a note for. The list now updates, but the note will remain in the exact cell into which they entered the text, which becomes confusing quite quickly.

What I would love to see is a way to make sure that the text that the user entered on Sheet4 sticks with the information that the LOOKUPs are generating and not necessarily just the cell in which it was initially entered.

2) I think it would be possible to restructure the document so that there is a "Note" column on each Sheet, so that it would be possible to pull out the "Note" column into the summary sheet with the same method used to get the other info.

I would prefer something like what is mentioned in option #1 above in order to conserve space on the individual spaces, but after writing it out, it does sound like option #2 will probably be a more simple solution.

Any insight? Thanks!

Yes, you're right that Option 2 would be much simpler.

Just a final wrap up post - I did cobble together a set of cells to attach to the individual sheets to act as a notes section. I then added in a bit of VLOOKUP and IF statements to comb the individual sheets and suck the notes in to Sheet4 to act as a reference sheet. It actually works out pretty well :-)

Thanks much, Bentleybob, I would still be struggling with this one without your help.

Cheers!