Thanks for letting me know.

2. Sorry I've just come across an issue. I need to delete some rows between Rows 19 to 63 (as I don't require all currencies for some of the activities) but when I do it the formulas in rows 5 to 13 break. ie they turn to #N/A. Do you know how I can fix this? Thanks again....

3. I have no idea if that is the best or even a good formula, but this change should overcome your problem

=IFERROR(INDEX(D\$19:D\$58,MATCH(1,(\$B\$19:\$B\$58=\$B5)*(\$C\$19:\$C\$58=\$C5),0)),0)+
IFERROR(INDEX(D\$19:D\$58,MATCH(1,(\$B\$19:\$B\$58=\$B5)*(\$C\$19:\$C\$58="AUD"),0))*INDEX(D\$62:D\$69,MATCH("AUD",\$C\$62:\$C\$69,0)),0)+
IFERROR(INDEX(D\$19:D\$58,MATCH(1,(\$B\$19:\$B\$58=\$B5)*(\$C\$19:\$C\$58="NOK"),0))*INDEX(D\$62:D\$69,MATCH("NOK",\$C\$62:\$C\$69,0)),0)+
IFERROR(INDEX(D\$19:D\$58,MATCH(1,(\$B\$19:\$B\$58=\$B5)*(\$C\$19:\$C\$58="EUR"),0))*INDEX(D\$62:D\$69,MATCH("EUR",\$C\$62:\$C\$69,0)),0)+
IFERROR(INDEX(D\$19:D\$58,MATCH(1,(\$B\$19:\$B\$58=\$B5)*(\$C\$19:\$C\$58="SGD"),0))*INDEX(D\$62:D\$69,MATCH("SGD",\$C\$62:\$C\$69,0)),0)

4. OK I'll try this, thanks. My only concern is I have to drag this across 16 columns and the file is large - I have found that array formula's can slow files down. I'll give it a go tho and let you know. Thanks again for all your help..

5. The fact that your real dataset is large perhaps ought to have been mentioned at the outset. If the array formula proves slow, you may need to look at a VBA solution.

6. Sorry yes I should have mentioned this. I don't have experience setting up macros but good time to learn! I was thinking if I put the rates in to another worksheet this may help simplify the formulas? Unfort I have to put it aside now for month end reporting. I still have a couple of weeks to finalise it so I will try a few things and come back to you with any further queries in about 1 week's time if that's ok? Thanks again for your time and help, very appreciated!

7. Here is a solution without VBA and without formulae, but using Power Query which should be fast.

I have deleted your results table as the PQ creates its own.

I have added two rows for USD to the exchange rates, so as to keep the format/structure, but to ensure the PQ merge finds a match.

I added a few values to other periods just to show it works correctly.

8. Sorry Bob I've been off work sick, back today. Thanks very much for that but I need to be able to replicate this for the real data (and understand it) so I will get online and try to learn power queries as I've never used this feature! I can do pivot tables - is this similar? I'll look online over the weekend anyhow.. thanks again!

9. No, Power Query is not like pivots. It is often used to create a dataset that is more pivot friendly than the unstructured or badly structured data that you often have to start with, but that is all. What it is is an in-memory ETL (Extract, Transform, and Load), so it takes data and manipulates it before loading the datamodel, which is what the pivot will use. It is very, very powerful.

10. OK thanks - I'll try to get head around it. In case I don't get up to speed with this in time to get this report out, I don't suppose you could suggest an alternative formula, if I were to put the fx rates in a different worksheet - do you think that might make a difference to the (length of) the formula that Ali suggested? If not I'll just use Ali's for now. Thanks again!

Page 3 of 4 First 1 2 3 4 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•