Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

I have the following function in the cell B2:

ARRAYFORMULA(SUM(IFERROR(Purchases!B3:B, 0)))

And the following function in cell C2:

ARRAYFORMULA(SUM(IFERROR(Purchases!C3:C, 0)))

Basically, the first column (Column B) are prices in USD, and the second one (column C) are prices in LBP (Lebanese Pounds)

In the rest of the cells in Column B and C (i.e from B3/C3 till the end) i have the following:

    +------+----------+----------+
    |COL: A|  COL: B  |  COL: C  |
+---+------+----------+----------+
| 1 | text |   text   |   text   |
+---+------+----------+----------+
| 2 | text |   SUM    |   SUM    |
+---+------+----------+----------+ 
| 3 | text | =C3/1500 | =B3*1500 |
+---+------+----------+----------+
| 4 | text | =C4/1500 | =B3*1500 |
+---+------+----------+----------+
| 5 | text | =C5/1500 | =B3*1500 |
+---+------+----------+----------+
| 6 | text | =C6/1500 | =B6*1500 |
+---+------+----------+----------+

I will try to explain the spreadsheet:

Column A, is the description of the product purchased, which is just plain text.

Column B and C are the price of the product in USD and LBP respectively. Now, when I buy a product, sometimes I purchase it in USD, sometimes in LBP. So I want to add in its respective column, and when doing so, the other column will be automatically calculated and filled. The circular dependency in these cells are not a problem to me and totally understandable.

Issue

My problem are the cells B2 and C2, the first time I wrote the function, it worked like a charm. But after closing the document, the result of the function is a #REF Error. But if I edit the cell, and rewrite the same function, it returns the correct value. This has to be done, every time I reopen the document.

Am I doing something wrong? Or is this just a bug?

Is there another/better way to accomplish what I want? (Which is, price is available in both currencies, and if I input one currency, the second one is calculated automatically. And a cell with the total value of each currency)

P.S. I tried inputting the formulas this way too:

SUM(ARRAYFORMULA(IFERROR(Purchases!B3:B, 0)))

instead of

ARRAYFORMULA(SUM(IFERROR(Purchases!B3:B, 0)))

But I get the same issue.

share|improve this question
add comment

1 Answer

up vote 3 down vote accepted

The pure spreadsheet answer is to create columns D and E to display your information. An example row:

A3: text
B3: (enter USD if known)
C3: (enter LBP if known)
D3: = if( isblank(B3) ; C3/1500 ; B3 )
E3: = if( isblank(C3) ; B3*1500 ; C3 )

The other way you can solve this is with JavaScript, if you know it, using the onEdit trigger. In that case the JavaScript would detect the edit to B3 and automatically add the formula in C3, or vice versa.

share|improve this answer
    
I didn't know it was possible to write JavaScript in the spreadsheets. Never even crossed my mind to search if it was possible. Cheers for the tip. –  Dan Dec 7 '11 at 8:56
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.