Good Evening!
I am using VLOOKUP to pull data from another sheet called "001A Hrs Summary" that contains data pulled from an external source. The problem I am running into is if the row on the "001A Hrs Summary sheet" that matches the VLOOKUP contains a blank cell directly below the name being looked up, then there is additional data in the second row, that belongs to the cell above it, that I need to populate next to the primary data in columns "J - Q" of the "001A Teams" sheet.
On the 001A Hrs Summary sheet, I highlighted the blank cells, which represents an individuals secondary qualification on an aircraft. The primary is what is listed directly across from their name, whereas one individual's primary (1st qual) may be AC2 another's primary may be AC1, and only if they have a secondary qualification will the 2nd row populate on the data sheet with a blank cell below their name.
In the sample below, Doe, John J. has a secondary qual (rows 8/9 of 001A Hrs Summary). It is that row, I am trying to populate in columns J - Q of the 001A Teams. The caveat to this is that the Hrs Summary is constantly updated by another office and names are constantly added and removed and quals added, so the rows Doe, John J. may be in today, may change tomorrow which is why I had to use VLOOKUP.
Is there any way to get the secondary qual to populate? If there is an easier way by automatically restructuring the Hrs Summary sheet, to possibly another sheet that will move the secondary next to the end of the primary line, I am open to it. I have been researching this tirelessly for days to no ends, and below is the best I could come up with.
Also, here is a link to the excel file on SkyDrive that I stripped down for security reasons. Columns A - Q are typically hidden.
I really appreciate any help I can get. I come here quite often when doing little projects that I get stumped on and this site has been a time saver.
Thanks again!
Excel 2010
<tbody>
</tbody>
<tbody>
</tbody>
Excel 2010
<tbody>
</tbody>
I am using VLOOKUP to pull data from another sheet called "001A Hrs Summary" that contains data pulled from an external source. The problem I am running into is if the row on the "001A Hrs Summary sheet" that matches the VLOOKUP contains a blank cell directly below the name being looked up, then there is additional data in the second row, that belongs to the cell above it, that I need to populate next to the primary data in columns "J - Q" of the "001A Teams" sheet.
On the 001A Hrs Summary sheet, I highlighted the blank cells, which represents an individuals secondary qualification on an aircraft. The primary is what is listed directly across from their name, whereas one individual's primary (1st qual) may be AC2 another's primary may be AC1, and only if they have a secondary qualification will the 2nd row populate on the data sheet with a blank cell below their name.
In the sample below, Doe, John J. has a secondary qual (rows 8/9 of 001A Hrs Summary). It is that row, I am trying to populate in columns J - Q of the 001A Teams. The caveat to this is that the Hrs Summary is constantly updated by another office and names are constantly added and removed and quals added, so the rows Doe, John J. may be in today, may change tomorrow which is why I had to use VLOOKUP.
Is there any way to get the secondary qual to populate? If there is an easier way by automatically restructuring the Hrs Summary sheet, to possibly another sheet that will move the secondary next to the end of the primary line, I am open to it. I have been researching this tirelessly for days to no ends, and below is the best I could come up with.
Also, here is a link to the excel file on SkyDrive that I stripped down for security reasons. Columns A - Q are typically hidden.
I really appreciate any help I can get. I come here quite often when doing little projects that I get stumped on and this site has been a time saver.
Thanks again!
Excel 2010
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | S | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 3 | Doe, John D. | AC1 | 185.1 | 69 | 185.1 | 0 | 0 | 0 | 0 | Doe, John D. | ||||||||
| 4 | Doe, John J. | AC2 | 1951.1 | 793 | 1851.1 | 0 | 92.4 | 0 | 7.6 | Doe, John J. |
<tbody>
</tbody>
001A Teams
Worksheet Formulas
<tbody> </tbody> |
<tbody>
</tbody>
Excel 2010
| A | B | C | D | E | F | G | H | I | J | |
|---|---|---|---|---|---|---|---|---|---|---|
| 1 | DIV | Name | A/C | Tot. Time | flights | Primary | Secondary | Instructor | Evaluator | Other |
| 2 | 001A | Doe, John D. | AC1 | 185.1 | 69 | 185.1 | 0 | 0 | 0 | 0 |
| 3 | Total Hours | 185.1 | 69 | 185.1 | 0 | 0 | 0 | 0 | ||
| 4 | 001A | Doe, John A. | AC2 | 303.6 | 117 | 303.6 | 0 | 0 | 0 | 0 |
| 5 | Total Hours | 303.6 | 117 | 303.6 | 0 | 0 | 0 | 0 | ||
| 6 | 001A | Doe, John W. | AC2 | 360.2 | 135 | 360.2 | 0 | 0 | 0 | 0 |
| 7 | Total Hours | 360.2 | 135 | 360.2 | 0 | 0 | 0 | 0 | ||
| 8 | 001A | Doe, John J. | AC2 | 1951.1 | 793 | 1851.1 | 0 | 92.4 | 0 | 7.6 |
| 9 | 001A | AC1 | 964 | 333 | 961 | 0 | 0 | 0 | 3 | |
| 10 | Total Hours | 2915.1 | 1126 | 2812.1 | 0 | 92.4 | 0 | 10.6 | ||
| 11 | 001A | Doe, John Z. | AC1 | 1069.5 | 385 | 984 | 0 | 76.7 | 7 | 1.8 |
| 12 | 001A | AC2 | 708.2 | 286 | 700.4 | 0 | 5.3 | 2.5 | 0 | |
| 13 | Total Hours | 1777.7 | 671 | 1684.4 | 0 | 82 | 9.5 | 1.8 | ||
| 14 | 001A | Doe, John C. | AC1 | 423.6 | 141 | 423.6 | 0 | 0 | 0 | 0 |
| 15 | Total Hours | 423.6 | 141 | 423.6 | 0 | 0 | 0 | 0 | ||
| 16 | 001A | Doe, Johnny C. | AC1 | 926.7 | 303 | 925.5 | 0 | 0 | 0 | 1.2 |
| 17 | 001A | AC2 | 115.1 | 54 | 115.1 | 0 | 0 | 0 | 0 | |
| 18 | Total Hours | 1041.8 | 357 | 1040.6 | 0 | 0 | 0 | 1.2 | ||
| 19 | 001A | Doe, John H. | AC2 | 521.6 | 215 | 521.6 | 0 | 0 | 0 | 0 |
| 20 | Total Hours | 521.6 | 215 | 521.6 | 0 | 0 | 0 | 0 |
<tbody>
</tbody>
001A Hrs Summary
