Conditional Formatting

[SayEnTor]

I am updating a badly written spreadsheet that compares two entered values from a different file. The goal set before me is to create a formula that calculates the two values by different constants and place them on this spreadsheet for later calculations. The challenge I'm having is making that cell stand out if the calculation finds one of those original two values to be greater than the other.

I thought I'd make either the text or the cell change colors to make this obvious.

The basic formula I'm starting with is: =IF(('originbook'#$'sheet'.027*0.15)>('originbook'#$'sheet'.o57*12); 'originbook'#$'sheet'.*0.25; 'originbook'#$'sheet'.*12)

As you can see, I'm not pre calculating the answer, plotting it, and then comparing it so Conditional Formatting does not work as it is in the Calc program.

If anyone else has done this, or has it in their notes somewhere, I'd be VERY VERY happy for you to share the answer with me.

Thank you in advance.

[Lupp]

I cannot see a valid external cell reference in your formula.
An external reference should look like

Code: Select all

'file:///C:/Users/Myself/Documents/AnyFolderName/AnyFileName.ods'#$Sheet1.P27

(on a Win system) e.g. The file URL may look differently if pointing to a resource located in a network. Only if the sheet-name is a syntactical monster (start with a digit, contain spaces or special characters ...) the single apostrophes are needed there.

I am not sure if I understood the other parts of the question clearly. However, I would suggest to calculated conditions depending on external references in helper cells if the calculation itself not is too "expensive". The condition used in the CF itself can then simply reference the result. If the cells to which a conditional format shall be applied contains a formula themselves, you may consider to use the STYLE function and omit CF.

[SayEnTor]

Yeah, I was trying to be considerate and remove the extraneous things we all know to be true. Suffice it to say I have both spreadsheets in the same directory and open at the same time (in different windows).

I'm hoping to avoid pre-calculating the sums in the logic statement since that would create two more sheets to this already large array of spreadsheets.

As far as my objective is concerned ... I'm trying to show which calculation was used to determine the number that is greater ... the number * 12 or the number * 0.25 ... without having to recalculate it on my own.

I will check out "STYLE" as soon as my pies are out of the oven.

[Lupp]

I seem to not understand.

Code: Select all

=IF(('originbook'#$'sheet'.027*0.15)>('originbook'#$'sheet'.o57*12); 'originbook'#$'sheet'.*0.25; 'originbook'#$'sheet'.*12)

Syntax for external references aside, the formula its badly abbreviated and "027" is not a cell address, but the number 27 written with a leading zero. Assuming the "o57" is intended to mean the cell in row 57 of column O, and the missing cell-addresses in the second half of the formula are meant to be the same as in the first half in the same order, there may also be the "0.15" in the first subexpression a typo, and "0.25" actually meant. If so, your formula should probably read

Code: Select all

=IF((File1Ref#$SheetX.O27*0.25)>(File2Ref#$SheetY.O57*12); File1Ref#$SheetX.O27*0.25; File2Ref#$SheetY.O57*12)

This formula, however, can be simplified to

Code: Select all

=MAX(File1Ref#$SheetX.O27*0.25; File2Ref#$SheetY.O57*12

Now I am not quite sure if you want to make the result accessible at all in a cell, or just to use it in one or another way for the condition of a CF.
Please take the time to be precise about this and everything. That's what I would consider to be considerate.
Otherwise helpers risk to waste their time.

[SayEnTor]

Awesome. That helps a lot already.

Now if I can just get the cell to tell me which it chose so I may determine at a glance if the data is using the *12 or the *0.25 multiplier. That is why I'm trying to find a way to change the color of the fill or the text and looking into CF to do it. I don't care how or to what (color, font, etc) as long as it is different enough to be visually notable.

And thank you.

Say
Honor Praemium Virtutis est

[Lupp]

This way the question seems to address a basic case of conditional formatting. The MAXimum itself is needed neither by the conditions nor otherwise.
You may study the attached example. There are some additional explanations included concerning the usage of external references.
The Calc guide should also contain examples for CF on this level.

(The latin language is still appreciated as the mother of Europe by many educated in a traditional way. However, this excludes the parts shaped under the Orthodoxy since the great schism. We presently have running a very valuable exposition in Mannheim under the title "Die Päpste und die Einheit der lateinischen Welt". As the English speaking world never succeeded in cutting its roots in the history of the Latin church, you may be interested. SEE: http://www.euromuse.net/en/exhibitions/ ... t-der-lat/)

[SayEnTor]

<Sigh>

I've gone over your response and example(s) and applied my formula in the CF fields choosing the RESULTS format (with both workbooks open) but to no avail. CF refuses to alter any cell using the formula with external workbook cell conditions. I also closed and opened the workbook AND forced recalculation (F9) with no noticeable results.

But now I must leave to work. I shall try again as I'm able today. REALLY hope I don't have to create tables - I would have to update ALL of them every day.