Excel: Keep format of a reference cell Solved

Question

Hello,
I have an Excel spreadsheet that includes several sheets referring to the first one (which serves as reference data). The color of the data is significant, but when I reference a cell from the first sheet in another sheet, the format does not carry over. (=Sheet1!B19).
Can anyone help me?
Thank you in advance
Alain
Configuration: Windows Vista / Firefox 3.6.15

mic13710 · Accepted Answer

It is impossible by standard.

When you create dependent cells (i.e. those that contain formulas, simple or complex, whose result depends on one or more other cells), there is no format relation from one cell to others. It is only a result of calculation, mathematical or logical. Nothing more.
When you write a formula, Excel tries to give the cell a display format (date, financial, time, etc.) that it thinks best fits based on the display format of the main data.
Similarly, if you create a sequence (such as a title row) and write in an adjacent cell, Excel will assume that you are trying to continue the sequence and will assign the format of the previous cell by default. Otherwise, the standard format is assigned.
If you copy/paste one or more cells, Excel will default to pasting the formats of the copied cell(s).
But in all cases, the simple formats assigned and validated remain as they are for the cell, regardless of the cell's content.

If you want specific formats based on cell values or calculation results (which seems to be your case), there are only 2 solutions:
either you apply conditional formats to the cell
or, if that is not sufficient, you will have to resort to macros.

Michel

alainjjb · Answer

Thank you Michel,
can I, in the formula of the destination cell, test the color of the source cell to apply the same color to it? Or should I use conditional formatting, and if so, how do I test the color of the source cell?
Alain

mic13710 · Answer

No, there is no built-in formula that allows you to reference the format of a cell.
You need to set up a conditional format in which you must refer either directly to the content of the cell using comparison methods (less than, greater than, equal to, between, etc.), or indirectly through a formula to be determined.
Moreover, a conditional formatting can only reference values from the same worksheet (no condition can be based on a cell or calculation from another sheet).
However, if the calculation is not too complex, you can name the cells on the first sheet and use those names in the conditional formats of the second sheet.
You can also use cells from sheet 2 where the formulas are linked to the data of cells from sheet 1 and use their results to apply conditional formats in sheet 2.

There are a multitude of possible combinations, but it is difficult to provide more information without having a working file and without knowing what conditions you want to implement.

Michel

alainjjb · Answer

Hello,
I achieved what I wanted with the following method:
1°) creating a function that determines the color number of the used font (accessing the code "alt + F11:
Public Function ColorCell(Cible As Range) As Variant
ColorCell = Cible.Font.ColorIndex
End Function
2°) adding a column in the cells of which I call this function "ColorCell(Sheet1!A1)". A column that I hid.
3°) using conditional formatting by testing the value of the returned color (in my case the font is red, so the value is 3).
There may be a simpler way, but it works.
Thanks again Michel
Alain

eriiic · Answer

Hello,

To avoid being limited by the number of conditional formats, and if it's to visualize the value and the formatting of a cell or a range from another sheet, you can also use the camera tool.
It gives you a live updated image, with all the attributes of the cell, which you can place wherever you want.
Workbook1.xls
eric

alainjjb · Answer

Hello,  Eriiic, what is this "camera" in Excel? I’ve been looking everywhere ... nothing. Thanks to everyone for your answers. Alain

alainjjb · Answer

Thank you all. I learned something again today. Have a great day and see you soon Alain

Excel: Keep format of a reference cell

7 réponses

Texture issues in enshrouded

Where to find 5 mechanics points? sims 2 ds

How to remove a stuck disc from ps3?

How to digitize my vinyl records

How to switch from creative mode to survival in minecraft?

Unknown debit card charge escot

Screen brightness issue - acer laptop

The sound is crackling on windows 7

Convert .wlmp to .avi

The sims 2: cheat code "maxmotives"