Retaining destination cell formats when copying by formula.
Posted on 2011-02-17
Im working with Excel 2003.
I have a formula in a cell which copies an entry from a static set of values, selecting the value with the OFFSET function.
in consecutive cells A1, B1, C1 etc. in a row I will have
=OFFSET('Static Set'!A1,0,7-AFixedValuel,,1), then
=OFFSET('Static Set'!B1,0,7-AFixedValuel,,1), then
=OFFSET('Static Set'!C1,0,7-AFixedValuel,,1) etc.
In this way, as "AFixedValue" changes, the "string" of consecutive static values appears in the destination cells.
My problem is that the destination cells have a coloured background, which is destroyed by the formula, 'cos the formula "copies" the formatting of the "Source" cell.
I can't use "Paste Special", as this is a Formula.
I was hoping there was a function like CELLVALUEONLY (or some such), so I couk rewrite the destination cell's formula as
Unfortunately, I can't find anything like CELLVALUEONLY as a function.
Have any of you good people found a workaround, or am I barking up a blue herring?