Hello,
I am new here and hope you can help with with my problem.
We have an output from attendance system (is mostly up to 200-300 rows) and need this data to use/input in another file without adding in manually and checking if assigned values to the correct cell.
I create connection with that file and want to work via refresh so I am looking for formula that will check "Line 1,2,..." -> then if its "Internal/Eternal/Temporary" -> and then specific group "A,B,C" and insert value from 2nd column.
Issues I can't do anything about it:
- all 3 conditions are in same column
- 2 IDs are being multiplied such as Internal/External or A,B,C ..
- locations are not fixed e.g. can be that one day there will be no External employee from Line 1 and subgroup B (so this row wont be in the output) so then also the only changeable condition such as Line 1,2... can be in another row
Found somewhere solution with INDEX/MATCH/INDIRECT formulas for 2 conditions such as:
=INDEX(B:B;MATCH(A4;INDIRECT("A"&MATCH($A$2;A:A;0)+1&":A50000");0)+MATCH($A$2;A:A;0))
Couldnt find out how to extend it into 3 condition system and dnt quite understand that INDIRECT formula there.
<tbody>
</tbody>
I am new here and hope you can help with with my problem.
We have an output from attendance system (is mostly up to 200-300 rows) and need this data to use/input in another file without adding in manually and checking if assigned values to the correct cell.
I create connection with that file and want to work via refresh so I am looking for formula that will check "Line 1,2,..." -> then if its "Internal/Eternal/Temporary" -> and then specific group "A,B,C" and insert value from 2nd column.
Issues I can't do anything about it:
- all 3 conditions are in same column
- 2 IDs are being multiplied such as Internal/External or A,B,C ..
- locations are not fixed e.g. can be that one day there will be no External employee from Line 1 and subgroup B (so this row wont be in the output) so then also the only changeable condition such as Line 1,2... can be in another row
Found somewhere solution with INDEX/MATCH/INDIRECT formulas for 2 conditions such as:
=INDEX(B:B;MATCH(A4;INDIRECT("A"&MATCH($A$2;A:A;0)+1&":A50000");0)+MATCH($A$2;A:A;0))
Couldnt find out how to extend it into 3 condition system and dnt quite understand that INDIRECT formula there.
| Line 1 | |
| Internal | |
| A | 5 |
| B | 6 |
| C | 8 |
| External | |
| A | 3 |
| B | 4 |
| C | 9 |
| Line 2 | |
| Internal | |
| A | 1 |
| B | 2 |
| C | 4 |
| External | |
| A | 2 |
| B | 2 |
| C | 2 |
| Temporary | |
| A | 4 |
| B | 3 |
| C | 1 |
<tbody>
</tbody>
