formatting (indentation, borders) based on values of 3 cells?

[deLockloire]

hello, i'd like to format a cell based on the values of 3 cells altogether.

explanation:

i have a sheet arranged in such a way that two tables can fit on a page (when printed). columns A and F have names of items, columns B and G are numeric values, columns C and H contain dates, while column D and I has names of currencies. the two tables (A:D and F:I) are completely separate (visually separated with column E). something like this:

<table style="border-collapse: collapse;" width="500"><tbody><tr><td style="text-align: center; width: 20%; border: 1px solid black;">A</td><td style="text-align: center; width: 10%; border: 1px solid black;">B</td><td style="text-align: center; border: 1px solid black;">C</td><td style="text-align: center; border: 1px solid black;">D</td><td style="text-align: center; width: 10%;">E</td><td style="text-align: center; width: 20%; border: 1px solid black;">F</td><td style="text-align: center; width: 10%; border: 1px solid black;">G</td><td style="text-align: center; border: 1px solid black;">H</td><td style="text-align: center; border: 1px solid black;">I</td></tr><tr><td style="padding-left: 10px; border: 1px solid black;">Item1</td><td style="text-align: right; border: 1px solid black;">26.98</td><td style="text-align: center; border: 1px solid black;">24/1</td><td style="text-align: center; border: 1px solid black;">USD</td><td>
</td><td style="padding-left: 10px; border: 1px solid black;">Item2</td><td style="text-align: right; border: 1px solid black;">55.10</td><td style="text-align: center; border: 1px solid black;">14/1</td><td style="text-align: center; border: 1px solid black;">USD</td></tr><tr><td style="padding-left: 10px; border: 1px solid black;">Item3</td><td style="text-align: right;">45.45</td><td style="text-align: center; border: 1px solid black;">25/1</td><td style="text-align: center; border: 1px solid black;">EUR</td><td>
</td><td style="background: none repeat scroll 0% 0% rgb(242, 221, 220); border-top: 1px solid black; border-bottom: 1px solid black; border-left: 1px solid black;">Header1</td><td style="background: none repeat scroll 0% 0% rgb(242, 221, 220); border-top: 1px solid black; border-bottom: 1px solid black;">
</td><td style="background: none repeat scroll 0% 0% rgb(242, 221, 220); border-top: 1px solid black; border-bottom: 1px solid black;">
</td><td style="background: none repeat scroll 0% 0% rgb(242, 221, 220); border-top: 1px solid black; border-right: 1px solid black; border-bottom: 1px solid black;">
</td></tr><tr><td style="background: none repeat scroll 0% 0% rgb(204, 204, 204); border-top: 1px solid black; border-bottom: 1px solid black; border-left: 1px solid black;">Header2</td><td style="background: none repeat scroll 0% 0% rgb(204, 204, 204); border-top: 1px solid black; border-bottom: 1px solid black;">
</td><td style="background: none repeat scroll 0% 0% rgb(204, 204, 204); border-top: 1px solid black; border-bottom: 1px solid black;">
</td><td style="background: none repeat scroll 0% 0% rgb(204, 204, 204); border-top: 1px solid black; border-right: 1px solid black; border-bottom: 1px solid black;">
</td><td>
</td><td style="padding-left: 10px; border: 1px solid black;">Item4</td><td style="text-align: right; border: 1px solid black;">12.00</td><td style="text-align: center; border: 1px solid black;">16/1</td><td style="text-align: center; border: 1px solid black;">JPY</td></tr><tr><td style="padding-left: 10px; border: 1px solid black;">Item5</td><td style="text-align: right; border: 1px solid black;">94.12</td><td style="text-align: center; border: 1px solid black;">28/1</td><td style="text-align: center; border: 1px solid black;">GBP</td><td>
</td><td style="padding-left: 10px; border: 1px solid black;">Item6</td><td style="text-align: right; border: 1px solid black;">39.45</td><td style="text-align: center; border: 1px solid black;">17/1</td><td style="text-align: center; border: 1px solid black;">USD</td></tr></tbody></table>
now, what i'd like to automatize is the following.
in any given row, if neither B nor C has any value, while A does,

1. remove indentation in column A (set it back to 0)
2. color column A:D
3. remove borders from inside (i.e., no borders between A:D)

similarly, if there are neither G nor H has any value, while F has, do the same, but for the F:I table (i.e., remove indentation in column F, etc.).

i tried to fiddle with conditional formatting, but i could come up with a solution only for action #2. am i missing something?

thanks for your help,
deL

 

[wolverineb]

You would need to set the conditional formatting for A1, B1, C1, and D1 separately. Basically, select A1 and make the condition something like:

=AND($A1<>"",$B1="",$C1="")

and set the formatting to what you want for A1 only. Then select B1 and enter the same formula but set the formatting to what you want in B, which is different from A.

etc.

Then you can fill the formatting down. I think the trick is having a different Cond. Format set-up for each column's cells.

 

[deLockloire]

hey, thanks. i haven't though of that, actually. with your suggestion, i can probably solve action #2 & #3. but i still cannot set the indentation. or at least i can't see any options for that under conditional formatting.

 

[wolverineb]

Yeah. I think you can't do that with conditional formatting. Might have to resort to VBA.