Any Excel Guru?

[andrej]

Hi all,

Is there any Excel guru that may help me with a complicated formula? I have been struggling with one my excel file and unfortunately for the past 2 days, I can not move on.

To start. I have two data columns, Currency and Amount. Rows are not as one, but there are several groups (all in the same rows). To illustrate, they go from V10:W15, from V19:W28...

In another column, I try to find currency and if that matches my criteria, I want for a value to appear (which is in each row).

I came up with a following (simple) formula:

=IF(+$V10="EUR",$W10,0)

Other currencies include: USD, JPY, CHF, INR, CZK, TRY, NOK, RUB...

It does its job well, however, problem starts when I insert a new row. Then my formula is lost, because it does not know that I have inserted a new row. There is no detection.
Same goes for deleting a row. I get the "#REF!" message.

One way to go around is to re-drag row with the start of the formula. However, I have to share this file as template and I fear that people will forget to re-drag rows. Do you know, how to 'tell' Excel to add/remove rows automatically?

Finally, a former colleague, that I can not reach anymore has created a formula that was (somewhat) working. Hence my task to create a new template. But I am lost when I investigate it (in order to replicate it in this new worksheet). If I try to copy and paste (Ctrl+Shift+Enter) I get "!#NUM" errors and it is very messy.

=INDEX($V$10:$W$68,SMALL(IF(($B$139=$V$10:$V$67),ROW($V$10:$V$67)-MIN(ROW($V$10:$V$67))+1,""),ROW(L1)),2)

Is there anybody that may help me out?

Thanks!

[Jeroen Hoppenbrouwers]

Why do you use the $ signs? They lock the variable (row/column indicator) down and prevent the formula to adjust itself if you move stuff around. Only if you really want that effect, you use a $ sign.

$V$10 will always be cell V10, even if you move what is in there one line down.

V10 will move to W10 if you move the cell one down.


Hoppie

[andrej]

Thanks Hoppie!

I tried what you have suggested, but the problem still persists. If I add new rows or delete rows, the formula is messed up. I have to investigate this further, and most likely will take some Excel class this summer.

Cheers,