Reference with formats; how to reference text and fill color of a cell?

Hello Chris

The background of a cell cannot be referenced or copied, only its contents. I will provide a detailed instruction how to get around the problem. But you might read my recommendation in the very last paragraph before starting. It might make life much easier for you.

Here is what you can do:
- Select the container containing your spreadsheet and in the Format menu assign Fill Stye Sheet "Transparent"
- Select all cells (or at least the ones with which you want to work with a background) and in the Format menu assign Fill Stye Sheet "Transparent" (of course the red background in cell C1 will disappear)
- Without changing the selection: in the Spreadsheet menu select "Append Plane" (all of the contents of your spreadsheet will disappear - but don't be afraid – nothing is lost!)
- Select cell C1
– In the Spreadsheet menu select "Get info …" and in the table "Arrangement" set width and hight of the cell both to "Manual" and click "OK"
– In the Spreadsheet menu select "Contents Type" and assign "Drawing"
- Click inside the cell C1 - its selection will disappear as you have clicked in the drawing (not the spreadsheet cell as such!)
- At the very top click on the toolbox (= Outermost Container)
- With the rectangle tool draw a rectangle in cell C1 (it will actually be drawn in the drawing which is placed in that cell) not bothering about its dimensions
- Assign it the Fill Style Sheet "Red Fill"
- Assign it the Line Style Sheet "Shrill Line" (which in fact assigns the border the same Fill Style Sheet as to the contents, i.e. "Red Fill")
- In the Windows menu open the palette "Object Coordinates"
- Enter the following values for left border, top border, width and hight: depending on whether you are working in cm or inches: 0/0/1/1 for inches, 0/0/3/2 for cm
- Select the container containing your spreadsheet
- In the Edit menu select "Duplicate"
- In the contents-pulldown-menu of the toolbar change "Spreadsheet" to "No Contents" and acknowledge that the copy of the spreadsheet may be dismissed
- Open your document's inventory by clicking on the squirrel
- From the inventory drag your spreadsheet on the duplicated (now empty) container
- With the shift key pressed select the original container such that both containers containing your spreadsheet are selected)
- In the Windows menu open the palette "Drawing Commands" and click on the first alignment symbol (Align left and top sides)
You have now two containers lying on top of each other. The front one contains plane 1 of your spreadsheet, the other one plane 2 (with the red drawing)

The formula "#C1" in cell A1 (of plane 1) will only get the formatted text. There are two methods of getting the background as well.

Mathod 1:
- With cell A1 selected using the pull down menu or the respective command in the Spreadsheet menu change the plane shown in the front container to "2"
- Enter the same formula and switch back the planes

Method 2:
- No formulas in cell A1 of any of the spreadsheet planes!
- Anywhere in your document add the formula
SetCell(Spreadsheet 1!#$C$1;Spreadsheet 1!$A$1)&SetCell(Spreadsheet 1![2]$C$1;Spreadsheet 1![2]$A$1)
- If you choose to place it in a spreadsheet cell: put it in a cell which is outside the part of your spreadsheet visible in the container.
- If you choose the option "graphic text" it will produce a VALUE!-error because a text cannot contain a drawing. Never mind! The formula will do its job correctly. Just define the graphic text to be "non printable". I would not hide it completely as you may need to change the formula and would have a difficult time finding it.

I hope I didn't miss a step.

Well, all this is not really straightforward. I actually recommend another approach to working with backgrounds: Use the Power Function "MPFSetRangeBknd(Range;FillStyle)" with which you can control the fill style of a cell or range of cells with formulas. I highly recommend to buy the RT addition "Martin's Power Functions". This RT-extension contains other valuable functions not available in the basic program. It is contained in the Additions-folder of your basic RagTime installation package.

Kind regards, Tom