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:
<table style="width: 439px; height: 90px;" border="0" cellpadding="0" cellspacing="0"><col style="width: 48pt;" width="64"> <col style="width: 74pt;" width="98"> <col style="width: 72pt;" width="96"> <col style="width: 78pt;" width="104"> <tbody><tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt; width: 48pt;" height="17" width="64">
</td> <td style="width: 74pt;" width="98">Front</td> <td style="width: 72pt;" width="96">Side</td> <td style="width: 78pt;" width="104">Rear</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">Aston-M</td> <td>Pass</td> <td>Pass</td> <td>Fail</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">Lotus</td> <td>Fail</td> <td>Pass</td> <td>Pass</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">Tesla</td> <td>Pass</td> <td>Pass</td> <td>Pass</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">Ferrari</td> <td>Pass</td> <td>Fail</td> <td>Fail</td> </tr> </tbody></table>
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:
<table style="width: 417px; height: 306px;" border="0" cellpadding="0" cellspacing="0"><col style="width: 48pt;" width="64"> <col style="width: 74pt;" width="98"> <col style="width: 72pt;" width="96"> <tbody><tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt; width: 48pt;" height="17" width="64">Aston-M</td> <td style="width: 74pt;" width="98">Rear</td> <td style="width: 72pt;" width="96">Note: Test Dummy fatality on initial rear impact</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">Lotus</td> <td>Front</td> <td>Note: Test Dummy ejected from vehicle</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">Ferrari</td> <td>Side</td> <td>Note: Test Dummy decapitation</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">Ferrari</td> <td>Rear</td> <td>Note: Test Dummy spontaneous combustion upon impact</td> </tr> </tbody></table>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!
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:
<table style="width: 439px; height: 90px;" border="0" cellpadding="0" cellspacing="0"><col style="width: 48pt;" width="64"> <col style="width: 74pt;" width="98"> <col style="width: 72pt;" width="96"> <col style="width: 78pt;" width="104"> <tbody><tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt; width: 48pt;" height="17" width="64">
</td> <td style="width: 74pt;" width="98">Front</td> <td style="width: 72pt;" width="96">Side</td> <td style="width: 78pt;" width="104">Rear</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">Aston-M</td> <td>Pass</td> <td>Pass</td> <td>Fail</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">Lotus</td> <td>Fail</td> <td>Pass</td> <td>Pass</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">Tesla</td> <td>Pass</td> <td>Pass</td> <td>Pass</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">Ferrari</td> <td>Pass</td> <td>Fail</td> <td>Fail</td> </tr> </tbody></table>
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:
<table style="width: 417px; height: 306px;" border="0" cellpadding="0" cellspacing="0"><col style="width: 48pt;" width="64"> <col style="width: 74pt;" width="98"> <col style="width: 72pt;" width="96"> <tbody><tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt; width: 48pt;" height="17" width="64">Aston-M</td> <td style="width: 74pt;" width="98">Rear</td> <td style="width: 72pt;" width="96">Note: Test Dummy fatality on initial rear impact</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">Lotus</td> <td>Front</td> <td>Note: Test Dummy ejected from vehicle</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">Ferrari</td> <td>Side</td> <td>Note: Test Dummy decapitation</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">Ferrari</td> <td>Rear</td> <td>Note: Test Dummy spontaneous combustion upon impact</td> </tr> </tbody></table>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!
