Hi All,
I am new to this forum, I have done a search but can not see any posts with the same issue, so here goes.
I have multiple documents each with 45+ sheets, 38 of which I need to get data from, I can not edit these sheets but I can add an additional one (summary Page). What I am trying to do is combine a Vlookup and a sum product in to a single cell on a summary page, If there are any easier ways of doing this please feel free to make a suggestion.
My Data:
<tbody>
</tbody>
What I need to be able to do is have one cell in a summary page that sum the following up:
What I need is a single formula for Type 1 price per sheet, so I need to do is:
multiple the quantity * the unit price *the currency (which needs to be looked up in another sheet),
then give me a sum for the complete sheet for Type 1 price in a single cell on a summary page, then the same for Type 2 and Type 3
so something like (but not) this:
Sumproduct=((AK20:AK100),AC20:AC100),Vlookup(AB20:AB100,'LOOKUPSHEET'!A1:B40,2,FALSE))
I hope this makes sense, If I have missed anything out please let me know.
Thanks,
Justin
I am new to this forum, I have done a search but can not see any posts with the same issue, so here goes.
I have multiple documents each with 45+ sheets, 38 of which I need to get data from, I can not edit these sheets but I can add an additional one (summary Page). What I am trying to do is combine a Vlookup and a sum product in to a single cell on a summary page, If there are any easier ways of doing this please feel free to make a suggestion.
My Data:
Row/Column | I | AB | AC | AD | AE | AK |
Part | Currency | Type 1 Price | Type 2 Price | Type 3 Price | Quantity required | |
20 | a | GBP | 1.50 | 3 | 6.25 | 1 |
21 | ab | EUR | 4 | 1 | 0.5 | 2 |
22 | b | USD | 3.30 | 3.30 | 7 | 1 |
23 | cg | GBP | 12.90 | 100 | 156 | 0 |
24 | ef | GBP | 0.90 | 0.85 | 12 | 4 |
.. | .. | .. | .. | .. | .. | .. |
.. | .. | .. | .. | .. | .. | .. |
.. | .. | .. | .. | .. | .. | .. |
100 | .. | .. | .. | .. | .. | .. |
<tbody>
</tbody>
What I need to be able to do is have one cell in a summary page that sum the following up:
What I need is a single formula for Type 1 price per sheet, so I need to do is:
multiple the quantity * the unit price *the currency (which needs to be looked up in another sheet),
then give me a sum for the complete sheet for Type 1 price in a single cell on a summary page, then the same for Type 2 and Type 3
so something like (but not) this:
Sumproduct=((AK20:AK100),AC20:AC100),Vlookup(AB20:AB100,'LOOKUPSHEET'!A1:B40,2,FALSE))
I hope this makes sense, If I have missed anything out please let me know.
Thanks,
Justin