Using lookup data in a formula


#1

I am trying to use Lookup data in a formula and can’t get it to work.

Table A is a list of print products, and column 1 are the unit costs pulled from linked table B, which contains unit costs for different page counts.

In another column on Table A, I want to multiply the unit cost by 25. I have tried doing this using a forumla, and I have tried it using the Rollup function, and both result in “error.”

Also another weird thing I noticed: If I format the the column in table B that contains the unit costs as a number, the Lookup data in table A gets rounded up to a 1! So I can’t get any meaningful sums when I group the data. The only way to get the correct numbers to show up is if I leave the column in Table B formatted as short text.

Weird!


#2

My first thought is to double check your lookup’s formatting and then see if the formula works (and also be sure your format of the formula is correct as well).

As far as the number formatting goes, you can specify the decimal places in the Field’s formatting options to prevent it from rounding.


#3

So what I’ve figured out pretty much immediately is that this problem has to do with my aside: I have it formatted as text so of course I can’t use it in a formula!

And the other problem had to do with the way I had my formula formatted. Head smack!


#4

Wrap your string with a VALUE() function and you can use it in your formula. :wink: