Conditional formatting of table based on values from different tabs

[oladunken]

Given a table in tab named Main:

__A__B__C
1 car1 15 30
2 car2 18 28


Then 2 tabs:
car1
car2

Based on values from specific cells in tab car1 I color 15 e.g. (B1) yellow and (C1) 30 in red.
So far so good. The conditional formatting setup for each cell horisontally (B1 and C1) for car1 consists of about 7 rules giving different formatting (colors).

One rule for car1 cell with value 15 (B1) and 30 (C1) respectively can be:

=(IF((car1!C32/37.5)>0.8,1)) giving a yellow color

So each car1 cell has same rules but retrieve info from different columns. This means I can drag lower right corner of cell with value 15 (B1) to the right and copy it to cell with value 30 (C1). So horisontally the copy/paste of formatting is easy.
On the next line (car2) I have to get data from car2 tab though, so I can't just copy/paste formatting from line above (car1). Or can I?

So my questions?
1) Can I copy/paste formatting also between car1 and car2 lines (eg B1 to B2) even though they are based on different data from different tabs?

2) Another option. Each line in Main sheet starts with same name as tab-name for which I need to retrieve data. E.g. A1 cell has same name (car1) as tab (car1) which I need to retrieve data for in B1.
Can I use this to make the copy/paste work by e.g. making rules using tab-name (from first cell to the left) in formula. In case how will the syntax be?

Original:
=(IF((car1!C32/37.5)>0.8,1)) giving a yellow color

Change to something like this with a correct syntax. "name in leftmost cell" to be replaced by some magic:
=(IF(("name in leftmost cell"<name from="" leftmost="" cell="" on="" this="" line="">!C32/37.5)>0.8,1)) giving a yellow color

Any ideas?

/B</name>

 

[Special-K99]

You can't use Condtional Formatting rules that refer to a different sheet,
The way round this is to Define a Name for the range on the different sheet and refer to that in the CF rule.

 

[oladunken]

You can't use Condtional Formatting rules that refer to a different sheet,
The way round this is to Define a Name for the range on the different sheet and refer to that in the CF rule.

Ok. Someone told me I could use option 2 in my original post with INDIRECT and something like:

=(INDIRECT(A2&"!C32")*37.5)>0.8

...since A-column lists exact names given to tabs I need to retrieve from.

It seems to almost work. However, copying down doesn't increment the A2& part to A3& for next line below in the conditional formatting rules.

 

[oladunken]

Ok. Someone told me I could use option 2 in my original post with INDIRECT and something like:

=(INDIRECT(A2&"!C32")*37.5)>0.8

...since A-column lists exact names given to tabs I need to retrieve from.

It seems to almost work. However, copying down doesn't increment the A2& part to A3& for next line below in the conditional formatting rules.

Sorry, I meant =(INDIRECT(A1&"!C32")*37.5)>0.8 and ".........copying down doesn't increment the A1& part to A2& for next line below in the conditional formatting rules."

 

[Special-K99]

Try

=(INDIRECT(A2&"!"&C32)*37.5)>0.8