Format and Hide

Control style and content, explicitly.

FORMAT(conditions, palettetruerange, palettefalserange, <valuetodisplay>, <remoterows>, <applyfont>, <applyborder>, <applyfill>)
Applies formats of palette and returns valuetodisplay to cell. If condition is TRUE, then applies formats from palettetruerange, otherwise palettefalserange.
  • conditions: Array or range of booleans, TRUE or FALSE. See Note #2.
  • palettetruerange: Range with formats for condition = TRUE. See Note #1.
  • palettefalserange: Range with formats for condition = FALSE. See Note #1.
  • valuetodisplay: Value returned by function.
  • applyfont: Apply font format of palette to cell (default = TRUE).
  • applyborder: Apply border format of palette to cell (default = FALSE).
  • applyfill: Apply fill format of palette to cell (default = TRUE).
Note #1: Changing the palette format will not trigger Excel to recalculate. Instead, modify the text within the palette to trigger reformatting of dependent cells.
Note #2: If ranges has the same count as remoterows, then each remoterow is checked against each condition. If conditions has count of one, then entire remoterows is applied.
Example:
Below the cell in 'Simple Table' is explicitly formatted per 'Legend' if the value exceeds 0.75. While Excel provides good Conditional Formatting, it is easily disconnected from copied cells and can become intractable.
docformat.png

HIDEROW(conditions, <valuetodisplay>, <remoterows>)
Hides rows and returns valuetodisplay to cell. If condition is TRUE, then rows are hidden, otherwise rows are made visible.
  • conditions: Array or range of booleans, TRUE or FALSE. See Note #1.
  • valuetodisplay: Value returned by function.
  • remoterows: Range of remote rows to hide. If omitted, hides cell with formula.
Note #1: If ranges has the same count as remoterows, then each remoterow is checked against each condition. If conditions has count of one, then entire remoterows is applied.

Last edited Apr 19, 2014 at 11:54 PM by chadspen, version 6