Cells Turn Red
Cells Turn Red
Help!!
I have a file that was built in Excel. It does have formulas in it.
I'm opening it in the spreadsheet function on OpenOffice. It looks fine and nothing looks out of place. However, when I input a number into the cell, the background of that cell turns red.
I don't understand why. Can someone help me?
I have a file that was built in Excel. It does have formulas in it.
I'm opening it in the spreadsheet function on OpenOffice. It looks fine and nothing looks out of place. However, when I input a number into the cell, the background of that cell turns red.
I don't understand why. Can someone help me?
OpenOffice 4.1.3 / macOS Sierra 10.12.6
Re: Cells Turn Red
Can you upload the spreadsheet? The forum has a 128k file size limitation for uploads, so if it's bigger than that, you'll have to upload it to a file sharing service like Dropbox, Google Drive, MediaFire
Without seeing the spreadsheet, my first guess is that there's conditional formatting for the cell(s) that turn red.
Without seeing the spreadsheet, my first guess is that there's conditional formatting for the cell(s) that turn red.
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
Re: Cells Turn Red
From your description, it may be that conditional formatting has been applied; click on a cell that shows this behaviour and select Format | Conditional Formatting from the top menu and see what is displayed. If you see something like the image below, with a condition checked and condition(s) existing, conditional formatting has been applied. If you wish to remove, select the range, choose Format | Conditional Formatting and uncheck the condition(s). Search Help - F1 - for conditional formatting for more information.
If this isn't the case, perhaps you could upload your file for someone to look at it - [Forum] How to attach a document here. Note maximum file size is 128K. If your file is larger, use a file sharing site such as Mediafire. The link also contains information on how to anonymise your document if it contains confidential information.
If this isn't the case, perhaps you could upload your file for someone to look at it - [Forum] How to attach a document here. Note maximum file size is 128K. If your file is larger, use a file sharing site such as Mediafire. The link also contains information on how to anonymise your document if it contains confidential information.
Edit: Add the image |
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.2.2; SlackBuild for 24.2.2 by Eric Hameleers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.2.2; SlackBuild for 24.2.2 by Eric Hameleers
Re: Cells Turn Red
Yes I can.RusselB wrote:Can you upload the spreadsheet? The forum has a 128k file size limitation for uploads, so if it's bigger than that, you'll have to upload it to a file sharing service like Dropbox, Google Drive, MediaFire
Without seeing the spreadsheet, my first guess is that there's conditional formatting for the cell(s) that turn red.
OpenOffice 4.1.3 / macOS Sierra 10.12.6
Re: Cells Turn Red
In the excel program, the cell that turns red also marks on the graph to show strengths and weaknesses as the school year advances. I would like to figure out how to make that work if at all possible. Thank you!
OpenOffice 4.1.3 / macOS Sierra 10.12.6
Re: Cells Turn Red
Please upload the original .xls version of the file too. (Delete the sensitive data from it.) Then we can try it with LibreOffice. The LO has a littlebit higher compatibility with the foreign fileformats, and with formatting properties of the Excel.In the excel program, the cell that turns red also marks on the graph to show strengths and weaknesses as the school year advances. I would like to figure out how to make that work if at all possible. Thank you!
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
-
- Volunteer
- Posts: 255
- Joined: Tue Apr 18, 2017 8:23 am
- Location: Germany
Re: Cells Turn Red
As mentioned by robleyd I could find a CF (conditional formatting) - see screenshot. There are lots of CF formatted cells. (Opened on LibreOffice 5.4.0.3.)
.
.
Click on screenshot to enlarge.
.
.
Click on screenshot to enlarge.
LibreOffice current versions 7 and OpenOffice 4.1.15
on LinuxMint 20 - 21 Mate, W10-64 pro
on LinuxMint 20 - 21 Mate, W10-64 pro
Re: Cells Turn Red
Slightly OT - today I need to learn something: how do you get to the Conditional Format manager ??
Re the spreadsheet; it has protected areas (at least the chart), hidden areas including columns L to BP and inserted OLE objects. It also seems that many of the formulae are broken as erbsenzahl noted - whether this is a result of opening an Excel file with AOO or some other cause I'm not yet sure.
Re the spreadsheet; it has protected areas (at least the chart), hidden areas including columns L to BP and inserted OLE objects. It also seems that many of the formulae are broken as erbsenzahl noted - whether this is a result of opening an Excel file with AOO or some other cause I'm not yet sure.
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.2.2; SlackBuild for 24.2.2 by Eric Hameleers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.2.2; SlackBuild for 24.2.2 by Eric Hameleers
-
- Volunteer
- Posts: 255
- Joined: Tue Apr 18, 2017 8:23 am
- Location: Germany
Re: Cells Turn Red
It does not open since the sheet is "under protection".robleyd wrote:how do you get to the Conditional Format manager ??
So first you have to unprotect the sheet (only click on Tools menu → Protect sheet).
After that I (View menu) showed Column & Row headers; I marked cell C55 (red background, as complained) and then started Format menu → CF... manage → edit and got the shown results (screenshot).
After that you can check the hidden columns and the chart and its data ranges. It seems to be a smart "mark editor", but I am not able to check all the functions and why you often find #REF! and Err:508 (result of: =IF(AQ2="";#N/A,AQ2)) - I've got no idea how to handle the # and the comma); and #DIV/0! (simple 0/0 and not defined; that's easier).
Hope I could help.
LibreOffice current versions 7 and OpenOffice 4.1.15
on LinuxMint 20 - 21 Mate, W10-64 pro
on LinuxMint 20 - 21 Mate, W10-64 pro
Re: Cells Turn Red
I found the same problems with the errors and also haven't yet worked them out. I have sent you a PM re the CF manager to keep this topic from becoming cluttered.
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.2.2; SlackBuild for 24.2.2 by Eric Hameleers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.2.2; SlackBuild for 24.2.2 by Eric Hameleers
Re: Cells Turn Red
AOO has no CF manager. It works like this: You select one or more ranges, say A1:B6 and X1:Y6, with one active cell, say A1. Then you call the CF dialog and enter formula expressions with relative references being relative to the active cell A1. If your active cell is Y6 and you reference any cell left or above Y6, you get #REF errors for all the cells where the reference is out of sheet limits. This is anolog to entering a cell formula with relative references into a range of cells with Alt+Enter.
The CF manager in LibreOffice takes the top-left cell of a cell range as the anchor of relative references. And quite often it produces strange results, particularly when you copy or move cells with CF.
The CF manager in LibreOffice takes the top-left cell of a cell range as the anchor of relative references. And quite often it produces strange results, particularly when you copy or move cells with CF.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Cells Turn Red
ok. Here it is.Zizi64 wrote:Please upload the original .xls version of the file too. (Delete the sensitive data from it.) Then we can try it with LibreOffice. The LO has a littlebit higher compatibility with the foreign fileformats, and with formatting properties of the Excel.In the excel program, the cell that turns red also marks on the graph to show strengths and weaknesses as the school year advances. I would like to figure out how to make that work if at all possible. Thank you!
OpenOffice 4.1.3 / macOS Sierra 10.12.6
Re: Cells Turn Red
This is a typical Excel sheet, cluttered with 227 different formattings in 615 disjunct cell areas. Even with MS Excel it would be extremely difficult to handle this awful formatting mess.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Cells Turn Red
#DIV/0!; if you use these formulae =IF(AU2=0;0;AU2/AT2) the error message will be gone AV2 is calculating two zero's
=IF(AQ2="";#N/A,AQ2)), i changed it in =IF(N(AQ2)=0;NA();"") so its display #N/A, you change it to something else like 0 or empty cell
#508 = missing a bracket; =IF(AQ2="";#N/A,AQ2)) it has one bracket to mush
cells AI10:AQ37= #ref!; are pointing to empty cells in LO v6.0.0
in AOO 4.1.4rc2 cells AI10:AQ37= #520 error; 'Internal syntax error' and 'Compiler creates an unknown compiler code.' from the help file's
hope this helps
=IF(AQ2="";#N/A,AQ2)), i changed it in =IF(N(AQ2)=0;NA();"") so its display #N/A, you change it to something else like 0 or empty cell
#508 = missing a bracket; =IF(AQ2="";#N/A,AQ2)) it has one bracket to mush
cells AI10:AQ37= #ref!; are pointing to empty cells in LO v6.0.0
in AOO 4.1.4rc2 cells AI10:AQ37= #520 error; 'Internal syntax error' and 'Compiler creates an unknown compiler code.' from the help file's
hope this helps
Libre Office 6.1 dev homebuild Open Office 4.1.5 on Slackware64 current