Excel 2003: How to show the same formatting of text of a referenced cell from another workbook

Flying_High_71
Flying_High_71 used Ask the Experts™
on
Hi EE

In Excel 2003, how do I show the same format of text of a referenced cell from another workbook?

I basically have two workbooks
 - The Master Version where I do my work and format the cells with bolds and coloured text to reflect the input from various contributers
 - The dumbed down version for the masses to ponder over, without providing them the opportunity of screwing up the Master version.

The dumb version basically references cells directly in the Master, but sadly the formatting and colours of the text which are present in the Master are not brought across to the dumb version, which detracts considerably from being able to read the text in the dumb version.

Is there any way I can force Excel to not only reference the text in the cell, but also bring along the formatting of the text within that cell i.e. the same as if I had performed a copy and paste of the entire cell?

The formula as it stands as present in the dumb verion  is akin to:

='[MasterWorksheet.xls]MasterTab'!D5

but i suspect thie only brings across the text of the cell. not the formatting within that cell.


cheerio
FH
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
JPIT Director

Commented:
It is not possible to do with a formula.

Author

Commented:
quite happy to hear how it can be done...
Top Expert 2010

Commented:
peetjh is correct: it cannot be done with a formula.

If you want to be able to keep the displayed text, that is possible with VBA.  Add a function like this:

Function DisplayValue(rng As Range)
    
    Application.Volatile
    DisplayValue = rng.Cells(1, 1).Text
    
End Function

Open in new window


Now use it like this in a formula:

=DisplayValue('[MasterWorksheet.xls]MasterTab'!D5)

Of course, that cannot bring back borders, italic, bold, font, font size, font color, fill color, etc.  That simply cannot be done with a function or formula.

Silly question, but why can't you simply give your users a copy of the "smart" file, whilst you preserve a safe and pristine copy of the master file where no one can mess with it?

Author

Commented:
Hi matthewspatrick

Thanks for the response.  I am not quite clear on what would be achieved with the display of the text value.


Is there not a similar function that can copy an entire range, you know something that might include a function like this:
Sub Update()
Workbooks("Master.xls").Sheets("Sheet1").Range("A5:J36").Copy
Workbooks("Slave.xls").Sheets("Sheet1").Range("A5:J36").PasteSpecial (xlPasteAll)
End sub

or thereabouts.  Note that the above does not actually seem to work as I am getting a Out of Range error, but hopefully you get my drinft.

In reponse to your 'silly question' query, which it isn't, the Master has a whole bunch of other info in it that serves a purpose in the technical team, but would only cause the masses to lose focus on what we want them to read. So the slave version only has choice bits in it.  Secondly, we are operating out of Sharepoint, and I have made it so I never need to worry about updating the slave version, because I know when it is opened it will immediately update with refreshed values.  Obviously I could just make and upload a new slave version every time I update the Master, but where is the fun in that? :-)
Awarded 2010
Top Expert 2013
Commented:
It can't be done purely with functions, but with a bit of trickery all the parts that are not functions can be hidden so it looks like it's all functions.
Since you can't copy formatting inside even user defined functions, the basic idea is to create a user defined function that stores the addresses of what needs to be copied. Then you can use the Worksheet_Calculate event to do the copying every time those cells are selected to be recalculated.
Put this code in the sheet that the formulae will be on in the slave workbook
Private Sub Worksheet_Calculate()
'Put the code in the module so it has access to the public variables
DoCopy
End Sub

Open in new window


Put this code in a module in the slave workbook
Option Explicit
Dim srcBooks As String, srcSheets As String, srcRanges As String, dstBooks As String, dstSheets As String, dstRanges As String
Function CopyAll(src As Range)

'Save all the address pieces for later
srcBooks = srcBooks + src.Parent.Parent.Name + ";"
srcSheets = srcSheets + src.Parent.Name + ";"
srcRanges = srcRanges + src.Address + ";"
        'Application.Caller refers to the cell in which the function resides
dstBooks = dstBooks + Application.Caller.Parent.Parent.Name + ";"
dstSheets = dstSheets + Application.Caller.Parent.Name + ";"
dstRanges = dstRanges + Application.Caller.Address + ";"

'Copy the text
CopyAll = src.Text
End Function
Sub DoCopy()
Dim srcBook As String, srcSheet As String, srcRange As String, dstBook As String, dstSheet As String, dstRange As String
Dim temp As String
Dim src As Range, dst As Range
While Len(srcBooks) > 1
    srcBook = TrimFirst(srcBooks)
    srcSheet = TrimFirst(srcSheets)
    srcRange = TrimFirst(srcRanges)
    dstBook = TrimFirst(dstBooks)
    dstSheet = TrimFirst(dstSheets)
    dstRange = TrimFirst(dstRanges)
    'Use these variables just so the lines aren't all super long
    Set src = Workbooks(srcBook).Worksheets(srcSheet).Range(srcRange)
    Set dst = Workbooks(dstBook).Worksheets(dstSheet).Range(dstRange)
    'Copy everything you want. Note: You can't just copy the whole thing since that would kill the formula
    'Even if you saved the formula and put it back, that would cause a recalc and a loop
    
    src.Copy
    dst.PasteSpecial xlPasteFormats
    'You could also do it like this to avoid the clipboard but it would take more lines.
    'dst.Font.Bold = src.Font.Bold
    'etc
Wend

'Note: the trimming also clears all the public variables so they are empty for the next run

End Sub

'This function removes everything up to the ; and returns it
Function TrimFirst(str As String) As String
Dim temp As String
Dim loc As Integer
loc = InStr(1, str, ";")
If loc > 0 Then
    temp = Left(str, loc - 1)
    str = Mid(str, loc + 1)
Else
    temp = ""
End If
TrimFirst = temp
End Function

Open in new window


I am also posting the working example files I used. Of course macros need to be enabled. Open master.xls first since it is referenced from the slave.
master.xls
slave.xls
Awarded 2010
Top Expert 2013

Commented:
So you just do something like =CopyAll('[master.xls]Sheet1'!$A$2) and the code behind it will do the rest.

Author

Commented:
Hi TommySzalapski:

Thanks for the formula and the working samples.  Apologies for the delayed response as I moved off managing that little project.  I finally now had an opportunity to try it first hand.  

So, it does work as you demonstrated, and it certainly seems to be far more complicated than I would have expected it to be.  Happy to award you the points for that.

Just note that if you use Tommy's formula as is, Excel will use the format of the first line of text in the 'source' cell (src) for the whole of the destination' cell (dst).  So if you have mixed text formats in the source cell, you will need to modify this code again to try and get across the mixed text formats into the destination cell- however I am not sure how to do that and perhaps Tommy would be so kind as to indulge us, if it's possible.

Many thanks.
Awarded 2010
Top Expert 2013

Commented:
The only reason it looks so complicated is because I had to parse out the address. There may be a better way.

I'm not entirely sure what you mean about lines. If you put the CopyAll function in a cell and drag it down, it will match the unique source formatting of the cell each function refers too. Are you using arrays or something? I would be more than happy to help you make it do what you want, can you post a sample worksheet showing the error?
Awarded 2010
Top Expert 2013

Commented:
If you want more people to look at it then just me (a lot faster) click the 'ask a related question' link and post it there. I'll try to answer it either way.

Author

Commented:
Hi Tommy

As regards lines, what I meant was you could have multiple text formats within the cell, e.g.
the first line being bold and
the second line of text being regular, and say
the third line of text in regular blue.  

With the code as posted above, whatever the text format is in the first line of text,  when it is copied to the desitnation cell as per the VBA script above, all the text in the destination cell will be in bold, i.e. the same text format as the first line of text in the 'source' cell.  It seems to ignore the fact that other text in that same cell is formatted differently.

I have posted a file here to show the sort of formatting that might be in a single cell of the 'source' that I am trying to replicate in the 'destination' i.e. copy in exactly the same way, using the exact text formats.
FH-Source.xls
Awarded 2010
Top Expert 2013

Commented:
Okay. I see what you are up against. The problem is that the actual content of the cell in the slave book is the formula. So the format is set on the whole cell and since the copy formats function only copies the format and not the text, it formats the whole cell in the slave what the first character in the master was.

If there is some way to store the formatting in the text itself, then you can do it. (I'm not positive this can be done).
If you can't do it that way, you would need to replace the entire contents of the slave cell (which would kill the formula). You could keep a hidden sheet that stores the links and runs the copying at the recalcs, but that would be far from optimal. I'll do some research on the first option and see what I come up with. Again, no offense if you open a related question so more able minds can work on this.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial