Hello everyone,
I have been struggling to find a more automated way in order to make a calculation the past few months and I would like your help. I will give you a more detailed description of what I want to achieve.
Table 1 is in file Filename001.xlsx and Sheet1
<tbody>
</tbody>
Table 2 is in file Filename002.xlsx and Sheet2
<tbody>
</tbody>
Table 3 is in file Filename002.xlsx and Sheet3
<tbody>
</tbody>
I write the following code in file Filename001 and Sheet1
=(C1+C2+C7+C9)/((C1/(VLOOKUP(B1,[Filename002.xlsx]Sheet2!$A$1:$Z$4,2,FALSE)))+(C2/(VLOOKUP(B2,[Filename002.xlsx]Sheet2!$A$1:$Z$4,2,FALSE)))+(C7/(VLOOKUP(B7,[Filename002.xlsx]Sheet2!$A$1:$Z$4,2,FALSE)))+(C9/(VLOOKUP(B9,[Filename002.xlsx]Sheet2!$A$1:$Z$4,2,FALSE))))
Basically I locate X1 manually each time. What I want to do is to replace the above code with an automated one. So far, the only thing I found a way to replace was
(C1+C2+C7+C9)
with
(SUMIF($A1:$A9,[Filename002.xlsx]Sheet3!$A$1,$C1:$C9))
Is it possible what I'm trying to do over here?
Any ideas are welcomed. Thank you in advance for your time.
I have been struggling to find a more automated way in order to make a calculation the past few months and I would like your help. I will give you a more detailed description of what I want to achieve.
Table 1 is in file Filename001.xlsx and Sheet1
A | B | C | |
1 | X1 | Y1 | Z1 |
2 | X1 | Y2 | Z2 |
3 | X2 | K1 | Z3 |
4 | X3 | M1 | Z4 |
5 | X3 | M2 | Z5 |
6 | X2 | K2 | Z6 |
7 | X1 | Y3 | Z7 |
8 | X2 | K3 | Z8 |
9 | X1 | Y4 | Z9 |
<tbody>
</tbody>
Table 2 is in file Filename002.xlsx and Sheet2
A | B | |
1 | Y1 | R1 |
2 | Y2 | R2 |
3 | Y3 | R3 |
4 | Y4 | R4 |
<tbody>
</tbody>
Table 3 is in file Filename002.xlsx and Sheet3
A | |
1 | X1 |
2 | X2 |
3 | X3 |
4 | X4 |
<tbody>
</tbody>
I write the following code in file Filename001 and Sheet1
=(C1+C2+C7+C9)/((C1/(VLOOKUP(B1,[Filename002.xlsx]Sheet2!$A$1:$Z$4,2,FALSE)))+(C2/(VLOOKUP(B2,[Filename002.xlsx]Sheet2!$A$1:$Z$4,2,FALSE)))+(C7/(VLOOKUP(B7,[Filename002.xlsx]Sheet2!$A$1:$Z$4,2,FALSE)))+(C9/(VLOOKUP(B9,[Filename002.xlsx]Sheet2!$A$1:$Z$4,2,FALSE))))
Basically I locate X1 manually each time. What I want to do is to replace the above code with an automated one. So far, the only thing I found a way to replace was
(C1+C2+C7+C9)
with
(SUMIF($A1:$A9,[Filename002.xlsx]Sheet3!$A$1,$C1:$C9))
Is it possible what I'm trying to do over here?
Any ideas are welcomed. Thank you in advance for your time.