Forum Discussion
extract data based on other cells
| Monday | Tuesday | Wednesday | Thursday | Friday | Saturday | Sunday | ||||||||||||||||||
| NAME | AGENCY | RATE OF PAY | WBS | FLOOR | HRS | WBS | FLOOR | HRS | WBS | FLOOR | HRS | HRS | WBS | FLOOR | HRS | WBS | FLOOR | HRS | WBS | FLOOR | HRS | WBS | FLOOR | TOTAL |
| Mr. A | OPTIONS | 19.5 | L146 | 4-B1 | 9 | L146 | 4-B1 | 8 | L144 | 4-B1 | 9.5 | L122 | 4-B1 | 9.5 | L122 | 4-B1 | 10.0 | L144 | 4-B1 | 10.0 | L122 | 4-B1 | 56.00 | |
| Mr. B | OPTIONS | 19.5 | L144 | 4-B1 | 8 | L128 | 4-B1 | 10.5 | L128 | 4-B1 | 9.5 | 9.5 | L128 | 4-B1 | 9.5 | L128 | 4-B1 | 8 | L142 | 4-B1 | 8 | L144 | 4-B1 | 63.00 |
| Mr. C | OPTIONS | 19.5 | L140 | 3-LG | 9 | L140 | 6-B3 | 9 | L142 | 4-B1 | 9.5 | L142 | 4-B1 | 9.5 | L142 | 4-B1 | 10.0 | L105 | 4-B1 | 10.0 | L105 | 4-B1 | 57.00 |
I have been given the table above as an example in order to try and extract the data into something easier to use for creating journals. the envisaged output is something like
| Mr. A | L146 | 4-B1 | 10 |
| L122 | 4B1 | 29 | |
| L144 | 4B1 | 10 | |
| total hours | 49 | ||
| ROP | 19.5 | ||
| £955.50 |
i need to pull out the number of hours based on the wbs code and the floor no. as this can appear multiple times in a row it would require addition as well. i have been advised that i can change the format of the original table if it will assist.
Is this something that can be done either by formula? i have tried combinations of index and match but i just couldnt fathom how to get them to work.
Would a macro work better? i have done some work on macros but nothing this detailed
Thanks in advance
Mark
No RepliesBe the first to reply