Keep formatting while using drop-down list

[SLW612]

Hello,
I have a spreadsheet where every three rows alternate between shaded and
non-shaded.

I am using data validation beginning in cell C5. My list (which is on a
separate sheet) looks like this:
Fruit Apple
Fruit Pear
Veggie Broccoli
Veggie Carrot
.... and so on.

So, the drop down list in C5 contains the second column of values, and when
you select one of them, whatever is next to it in the first column will
automatically display in B5 (e.g. you select 'Pear' and 'Fruit' automatically
appears because of the 'offset' function).

My question is this: the cell with the automatic value, B5, does not keep
the same formatting as the cells around it. How can I copy the formatting
from column A or C when using this offset function? I don't know how to do
conditional formatting to match the formatting next to the cell.

Hope this makes sense! Thanks in advance

 

[ShaneDevenshire]

Hi,

When I test your idea I do not loose the formatting in the cell?
I have a Data, Validation list in F3:F7 and the formula below is in E3:E7:

=IF(F3="","",OFFSET(Sheet6!A$1,MATCH(F3,Sheet6!B$1:B$4,0)-1,0))

Format is unaffected you picking an item from the list.

Cheers,
Shane Devenshire

 

[SLW612]

Thanks for your reply Shane.
I tried your formula but I am still losing the shading in the cells with the
formula. I don't know why you are not. The formatting for the dropdown list
is kept, but that automatic value is not.

 

[ShaneDevenshire]

Hi,

Either I don't understand your question correctly or I will need to see a
sample file with the problem.

If you want you can send one to (e-mail address removed)