Hi
Mangesh
Answer to your question all the customer name will be stored in one column
(i.e column B) in sheet 1 only.
The table will be in sheet 2.

Table which I say will be in sheet 2, it was in sheet 1 before. It was in
column AD, AE & AF. Everything was working fine. I used conditional
formatting under column heading B. What I have done before
Condition 1
Formula is
=OR(B4=$AD$3,B4=$AD$4,B4=$AD$5,B4=$AD$6,B4=$AD$7,B 4=$AD$8,B4=$AD$9)
Condition 2
Formula is
=OR(B4=$AE$3,B4=$AE$4,B4=$AE$5,B4=$AE$6,B4=$AE$7,B 4=$AE$8,B4=$AE$9)
Condition 3
formula is
=OR(B4=$AF$3,B4=$AF$4,B4=$AF$5,B4=$AF$6,B4=$AF$7,B 4=$AF$8)

Each formula represents one class of customer.
Now I want to just change my formulas . If you see 1st formula
B4=$AD$3,B4=$AD$4,................................ ..
Formula 1 change requirement
$AD$3 should be replaced by the sheet 2 column A row 3 cell reference
$AD$4 sholud be replaced by the sheet 2 column A row 4 cell reference
$AD$5 should be replaced by the sheet 2 column B row 5 cell reference.
Formula 2
B4=$AE$3,B4=$AE$4, .....................
formula 2 change requirement
$AE$3 should be replaced by sheet 2 column B row 3 cell reference
$AE$4 should be replaced by sheet 2 column B row 4 cell reference
$AE$5 should be replaced by sheet 2 column B row 5 cell reference

And this way same for formula 3 as well.
I hope for the correct solution!!
Thanking you in anticipation!!