Replies

Hi Phil,

Yes, he omitted the 1 in front. THe post is actually 169610.

Mike, thanks for pointing it out for me, it’s very useful in understanding the anchors as well.

Regards,
K.

Thanks a lot for all your replies. I am going through all the suggestions, some do seem to help, but not for all pictures alike. I also found that if I save the file as jpg and insert it from a file, it works (but again not for all of them).

Regards,
K.

Thanks, it works now.

K.

That’s what I want as well, but as soon as I group the second set of columns, they will get grouped together with the first one, as it was all one single group.
Could you possibly post a small xls file to show the result of what you got?
Thx,
K.

Thanks a lot, you saved the day!
Poor guy was working for a week now on this database and he was already pulling his hair out!

Best regards,
K.

Lovely, thank you very much, all your suggestions work! I just have to modify it to work with the real thing, and I have a headache less to deal with, lol.

Thanks again.

Bye,
K.

Hi Jan,

I must be doing something wrong, as the result for the formula you gave me is an empty cell. Of course, if I enter numbers in all the cells that it looks for (A3 until the end), I have the correct summation. However I want to add up the actual cells that have numbers in them, and to ignore the empty ones, as the client does want an updated sum which will change every month, depending of the new entries done…

Thanks a lot,
K.

That’s ok, what you told me now it does help a bit, I will use it.

Thanks again for all your help!

Regards,
K.

Hi Glenn,

It’s actually a great way, thanks a lot, I will keep it in mind for the future. Right now I cannot use it, however, because all I want is to make my own life easier and select multiple cells to paste/link. I cannot use the transpose formula in the other sheet, as that goes to a client, and they will be wondering what is that strange formula, when all they need is a link. Also I think if I use an array formula, I cannot modify later single cells within that array, and I might need to do so.
So I guess I’ll just copy/paste link the single cells one by one, although they are so many of them…

Thanks anyway.

Bye,
K.

Hi John,

I tried your formula, but I still got the Div/0 error. I made a very small sample of my workbooks, and I put everything in one sheet (in the original I am using different sheets, different files) for easier use. I am attaching it with my comments included.
Any help would be greatly appreciated.

Thanks,
K.

I constructed the formula that you gave me for another 4 periods data, and the final would look like this:
(I renamed the Exchange rate file to Test, for easier overwiew of the formula)

=IF(OR([Test.xls]Sheet1!$E$9=0,[Test.xls]Sheet1!$E$10=0,[Test.xls]Sheet1!$E$11=0,[Test.xls]Sheet1!$E$12=0,[Test.xls]Sheet1!$E$13=0,[Test.xls]Sheet1!$E$14=0),””,Sheet1!K6/[Test.xls]Sheet1!$E$9+Sheet1!L6/[Test.xls]Sheet1!$E$10+Sheet1!M6/[Test.xls]Sheet1!$E$11+Sheet1!N6/[Test.xls]Sheet1!$E$12+Sheet1!O6/[Test.xls]Sheet1!$E$13+Sheet1!P6/[Test.xls]Sheet1!$E$14)

The results of this gives me an empty cell. I might have made a mistake when I continued the given formula for the remaining 4 months.
Basically I want to sum up data for 6 months, each of them using a currency exchange rate which is in another file (whereby I take the local currency data and divide by the exchange rate), and in the end to add up the final 6 numbers to a total.

Thanks a lot,
K

Yes you are right. Thanks for the tip, it helped.
The problem was exactly the lack of one zero in my range.

K.

Thanks for your reply, it does help me. It’s an internal document, so it doesn’t matter if it all looks like a big paragraph, in this case really reducing the number of the pages is important.
I didn’t realise the Find/Replace has all these extra options, I will next explore all of them, as they seem very useful indeed!
Cheers,
K.

Cheers to everybody!
I want to say a great thanks to all of you. Some gave me a direct help, others useful tips which are worthwhile keeping.
I will print out the whole thread and chew through it carefully.
You are the greatest!

K.

Thanks for all your responses, they are very useful. I think, however, the the best one that suits my needs is the custom cell formatting, because it seems the easiest solution from all. I will keep the macro in my list of useful macros, but for now, I simply took the formatting given and modified it to also show the negative numbers with red font and in brackets. I have a very minor problem with it though, that even the 0 is shown as negative now. I can’t figure out why… Can you please help me again?
Here is what I did so far:
#,##0;[=-100]”NA”;(#,##0)

Thanks again for all your help.
K.