| View previous topic :: View next topic |
| Author |
Message |
nejko
Newbie
Joined: 25 Jan 2005
Posts: 3
|
 Posted: Tue Jan 25, 2005 1:59 pm Post subject: A function problem |
 |
|
Hi,
I have an Excel workbook with such a function:
| Code: | | =DSUM(BRUTOBIL!$A$1:$K$1800;"SALDE";INDIRECT(CONCATENATE("BSDefs!";B33;"1")):INDIRECT(CONCATENATE("BSDefs!";B33;"30")))/1000 |
Of course in Excel it works fine. Now I'd like to translate it to work in OOo Calc:
When I opened the .xls file in Calc, the function I got was this one:
| Code: | | =DSUM($BRUTOBIL.$A$1:$K$1800;"SALDE";#NAME!(INDIRECT(CONCATENATE("BSDefs!";B33;"1"));INDIRECT(CONCATENATE("BSDefs!";B33;"30"))))/1000 |
So looks like Calc messes something up (a "$" in front of "BRUTOBIL" worksheet name and "#NAME!" there in the middle). OK, I cleaned it up and replaced "!" with "." since Calc uses dot to reference to other worksheets AFAIK:
| Code: | | =DSUM(BRUTOBIL.$A$1:$K$1800;"SALDE";INDIRECT(CONCATENATE("BSDefs.";B33;"1")):INDIRECT(CONCATENATE("BSDefs.";B33;"30")))/1000 |
Still, this formula doesn't work. Looks like condition can't be composed of two "INDIRECT" calls like in Excel. Any ideas how to deal with it? If I replace the condition with:
| Code: | | BSDefs.F1:BSDefs.F30 |
everything works fine and the result is correct.
Thank you very much for any help. If I solve this I don't have to buy MS Office. |
|
| Back to top |
|
 |
richhill
OOo Advocate
Joined: 16 Jun 2004
Posts: 418
Location: Mesa, AZ
|
| Posted: Tue Jan 25, 2005 8:40 pm Post subject: |
|
|
Is this what you want the INDIRECT and CONCATENATE functions to generate??
=DSUM(BRUTOBIL.$A$1:$K$1800;"SALDE";BSDefs.F1:BSDefs.F30)
_________________
OOo Calc tips: http://www.openofficetips.com |
|
| Back to top |
|
|
nejko
Newbie
Joined: 25 Jan 2005
Posts: 3
|
| Posted: Wed Jan 26, 2005 8:26 am Post subject: |
|
|
| richhill wrote: | Is this what you want the INDIRECT and CONCATENATE functions to generate??
=DSUM(BRUTOBIL.$A$1:$K$1800;"SALDE";BSDefs.F1:BSDefs.F30) |
Actually, yes. If i remove INDIRECT function calls (leave only CONCATENATE), I get error 508 again. Hints?
Thanks. |
|
| Back to top |
|
|
richhill
OOo Advocate
Joined: 16 Jun 2004
Posts: 418
Location: Mesa, AZ
|
| Posted: Wed Jan 26, 2005 9:16 am Post subject: |
|
|
Error 508 is missing bracket(s) - check the revised formula - that number of "(" matches number of ")".
_________________
OOo Calc tips: http://www.openofficetips.com |
|
| Back to top |
|
|
nejko
Newbie
Joined: 25 Jan 2005
Posts: 3
|
| Posted: Thu Jan 27, 2005 5:11 am Post subject: |
|
|
| richhill wrote: | | Error 508 is missing bracket(s) - check the revised formula - that number of "(" matches number of ")". |
I pasted you revised formula and as you can see all the brackets are in place. I lost a few hours on that and I triple checked every bracket but no luck.
Any other ideas? I just assume that D-functions (DSUM is one of them) just don't support such types of arguments for conditions as in Excel - it must be Workbook.FIELD:Workbook.FIELD style and it can't be composed of functions. Is this correct? Any ideas how to make an alternative which would achieve the same as in Excel?
Thanks. |
|
| Back to top |
|
|
|
