Ask Your Question
0

getting err 504 with complex function

asked 2018-01-11 19:06:45 +0200

dhusky gravatar image

I am using a file created in excel and when I go to use the file in calc I am getting err 504 from many formulas. The formulas use info contained inside sheets and tables in the file.

=IF(ISBLANK(B9),"",VLOOKUP(B9,grains_table[#all],6))

edit retag flag offensive close merge delete

Comments

Welcome to the forum, but ...
Do you expect someone here to know what the grains_table is supposed to mean?
To get help you will need to supply more information. Based on what you posted, you shouldn't expect a useful answer.

Lupp gravatar imageLupp ( 2018-01-11 19:26:56 +0200 )edit

The other person who answered provided a very useful answer, maybe i didnt give the best information but it was enough for someone else to figure out. They understood from the formula that 'grains_table' was a defined range/array.

dhusky gravatar imagedhusky ( 2018-01-14 04:48:57 +0200 )edit

I also guessed this. In fact I had already prepared a working example. However grains_table[#all] isn't correct/meaningful synatx in Calc, I would still want to know in what way this kind of subexpression can be used in Excel.
BTW: I much appreciate "the other person" who is @Jim K .
(Better information is better than probably sufficient information.)

Lupp gravatar imageLupp ( 2018-01-14 20:51:17 +0200 )edit

2 Answers

Sort by » oldest newest most voted
1

answered 2018-01-11 20:42:58 +0200

Jim K gravatar image

updated 2018-01-11 20:46:32 +0200

Remove [#all] from the formula and make sure grains_table is defined.

To see how this works, start with an empty spreadsheet. Put table data in C1 to H7 such as shown in the image below. Then go to Data -> Define Range and define the name grains_table as $Sheet1.$C$2:$H$7. Enter "oats" (without quotes) in B9 and the following formula in B10.

=IF(ISBLANK(B9);"";VLOOKUP(B9;grains_table;6))

grains_table vlookup

See also VLOOKUP documentation for LibreOffice.

edit flag offensive delete link more

Comments

That is what i ended up doing through playing around with it and making sense of each part.

dhusky gravatar imagedhusky ( 2018-01-14 04:42:03 +0200 )edit

Glad it helped. Please mark the answer correct, as explained under guidelines for asking.

Jim K gravatar imageJim K ( 2018-01-14 06:01:28 +0200 )edit
0

answered 2018-01-11 20:49:01 +0200

Lupp gravatar image

updated 2018-01-11 20:53:35 +0200

I may provide this little demo document in addition.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2018-01-11 19:06:45 +0200

Seen: 748 times

Last updated: Jan 11 '18