INDIRECTfunction and formatting

[Lewis Clark]

Hello, All

After getting talked out of using Master Documents in Word (they apparently
cause a lot of problems), I am trying to set up a series of about 7
documents in Excel 2003 - syllabi for classes I teach. Most of the contents
are common to each of the 7 syllabi, but there are a few differences. My
goal here is to facilitate the process of making changes to multiple
syllabi.

My current plan has a 2-sheet workbook. One sheet will contain all of the
component paragraphs with each syllabus in its own column. As changes are
needed I can just copy them across the columns as required.

The other sheet will have the master, assembled document. On the master
sheet, cell B2 holds the column letter of the class whose syllabus I want to
compile. I use the following function call to copy the contents from the
component sheet to the master sheet. I have this function copied down to
all the rows that need it (the "18" will vary of course) :

=INDIRECT("Components!"&$B$2&"18")

The text comes over fine, but some of the formatting is lost. I have some
of the text in each cell bold or underlined or in a different color, for
example.

Is there a way to do this while keeping all of the formatting from the cells
on the component sheet?

Thanks in advance for any assistance.

 

[Tom Ogilvy]

No. Rich text formatting is not supported for text produced via formula.

 

[Harlan Grove]

No. Rich text formatting is not supported for text produced via formula.

....

The OP's stated task is almost pure text processing. Storing the master
syllabus and exceptions in a database and rendering HTML pages from that
database containing specific syllabi would be the ideal approach.
Spreadsheets aren't always the best tool for every task, and they're pretty
poor at most text processing tasks.

If the OP only has Excel, then it's possible to generate HTML text from
Excel worksheet formulas, print such worksheets as text files with .HTML
extensions. Not WYSIWYG, and not direct from Excel to printer, but the
closest the OP will get without VBA.

As for VBA, an alternative would be to have formulas return the addresses of
each passage as text, then run a macro that would copy the worksheet
containing the textrefs and replace the textrefs with copies (value and
format) of the referenced cells.

 

[Lewis Clark]

Yes, it is pure text processing, which is why I first looked at using Master
Documents in Word. I don't need to generate HTML - I would just make a .PDF
file directly from Excel and publish the .PDF file.

I have all of Office 2003 Professional Edition, but have rarely used any of
the applications other than Excel and Word. I was hoping to stick with what
I know rather than learn a new application just for this.

I don't know VBA. Would the macro code be easy enough for you to show me
how?

Thank you very much Tom and Harlan for your replies.

 

[Harlan Grove]

I don't know VBA. Would the macro code be easy enough for you to show me
how?

....

The following is a very simplified version of what I mean.


Sub foo()
Dim wss As Worksheet, wsd As Worksheet
Dim s As Range, sa As String, d As Range

Set wss = ActiveSheet
Set wsd = ActiveWorkbook.Sheets.Add(After:=wss)

wss.Range("A1", wss.UsedRange(wss.UsedRange.Cells.Count)).Copy
wsd.Range("A1").PasteSpecial Paste:=xlPasteValues

On Error Resume Next

For Each d In wsd.UsedRange
sa = d.Value
Set s = Nothing
Set s = Range(sa)

If Not s Is Nothing Then
d.Value = s.Value
s.Copy
d.PasteSpecial Paste:=xlPasteFormats

End If

Next d

End Sub


You'd use formulas like

=CELL("Address",Sheet1!A1)

in the cells in the initially active worksheet that you want to appear in
your PDF output files rather than formulas calling INDIRECT.

 

[Lewis Clark]

Thank you very much!

...

The following is a very simplified version of what I mean.

<snip>