[Solved] Referencing Sheets

[hwgill]

I'm trying to reference a cell in a different sheet, but the same file, and it gives me an Invalid Value error if I click out of the cell, or click the green check. OO crashes if I attempt to switch sheets.

The value I'm trying to get is a summed value, to be placed on a summary page. According to the help file, it should be entered as =pagename.cell
I've tried it that way, and with parentheses and single quotes, but always get the same error.

The document is a Cash Flow Projection for the bank, so I need it working fast!

Help!

[Zizi64]

A simple example:

Code: Select all

=$Sheet1.A1

A tip:
Do not use special characters and spaces in the name of the sheets.

[FJCC]

As an example, the attached file has in cell A1 of Sheet1 a reference to cell A9 of Sheet2.

[hwgill]

I've attached the file I'm working with. The suggestion of using a $ sign made no difference. Invalid Value is the error that shows up still

[FJCC]

I see references to other sheets on the sheet Cash_Flow in cells B44, I35:X35 and X24. None of them are showing an error and a spot check of the displayed values shows that they are correct. Are all of those cells showing you an error or just a particular one?

By the way, there is no need to use the SUM function in row 35. You can just use formulas like

Code: Select all

=HardCosts.H77

[hwgill]

I see references to other sheets on the sheet Cash_Flow in cells B44, I35:X35 and X24. None of them are showing an error and a spot check of the displayed values shows that they are correct. Are all of those cells showing you an error or just a particular one?

By the way, there is no need to use the SUM function in row 35. You can just use formulas like

Code: Select all

=HardCosts.H77

B44 was a test. I35:x35 works fine. It's the ones from rows 19-27 that generate the Invalid Value error when leaving the cell, and crash OO when attempting to switch to another sheet with a Bad Allocation fatal error when two spreadsheet files are open, and a Vector <T> too long fatal error if only the one spreadsheet is open.

[FJCC]

Your formulas are correct, so I would try two things.
1. Copy the content of your file over to a new file and see if the problem continues.
2. Reset your user profile. To do that, shutdown OpenOffice. Open any folder and enter %appdata%\openoffice\4 in the address bar near the top. That will take you to a folder containing a folder named user. Rename that folder to user_old. Restart OpenOffice and you should get the registration screen. Is the file stable now?

[Zizi64]

It's the ones from rows 19-27 that generate the Invalid Value error when leaving the cell

The reason of this behavior is that
- the cells have Data Validity setting: "Decimal",
- and the Auto Input feature wants fill the cell with date like TEXTS (based on a cell content above)
when I type in only one number into these cells.

Possible solutions (workarounds):
- Switch OFF the Auto Input feature,
- or switch OFF the Data Validity feature,
- or try to use numeric Date values on the Date rows.

[hwgill]

It's the ones from rows 19-27 that generate the Invalid Value error when leaving the cell

The reason of this behavior is that
- the cells have Data Validity setting: "Decimal",
- and the Auto Input feature wants fill the cell with date like TEXTS (based on a cell content above)
when I type in only one number into these cells.

Possible solutions (workarounds):
- Switch OFF the Auto Input feature,
- or switch OFF the Data Validity feature,
- or try to use numeric Date values on the Date rows.

Thank you. Data Validity means that I can't put a formula in, and that's why it was choking on me. I changed the Data Validity to All and now it works fine.
Why the accountant did that...makes no sense, because when it's set like that you constantly have to switch sheets to update your data. Forget one time and it's messed up.

Again, thank you for figuring that out.

[miasma.gajs]

*cough*
Tag your thread when it has been [Solved]