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))
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.
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.
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 @jimk .
(Better information is better than probably sufficient information.)
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))
See also VLOOKUP documentation for LibreOffice.
That is what i ended up doing through playing around with it and making sense of each part.