format question when in a formula and nested VLOOKUP, OFFSET and HLOOKUP formula

I have a seemingly simple format question that I cannot seem to get around.
As I work in a scientific discipline (nutritionals and pharmaceuticals) we manipulate a lot of complicated formulations and chemicals and specifications.
If I have a chemical formula such as the following:

Na2B4O7•10H2O (imagine that the 2, 4, 7, and 2 are all subscript characters, like a real chemical formula).

They are formatted as a typical chemical formula would appear, by selecting the 2, 4, 7, and 2 and changing the font style to subscript and then pressing enter.

Excel allows you to format text characters having different font styles and sizes through the data entry bar.

Say this formatted text string appears in cell A1. In another workbook, I wish to add this text cell, complete with it's format to the concatenation of another string.

for example, I would have a formula that says

="Potency test for boron in "&'Sheet1'!$A$1

This works fine, but when Excel concatenates these two strings, it removes all the subscripts and formatting and the resulting cell will display

Potency test for boron in Na2B4O7•10H2O

(all the subscripts appear as regular numbers now)

How would I be able to preserve the exact formatting when I am bring in a multi-character formatted text string when used in a formula?

As a side note, I work with some pretty eye-sore ugly complicated nested functions and I think one of the most powerful functions in Excel is the OFFSET function combined with VLOOKUP and HLOOKUP. They are great when you work with lots of products that have numerous specifications, and the specifications have specifications and test methods within themselves that you are trying to organize. I've mainly learned how to use these by trial and error, but once you have mastered them, you can truly generalize the manipulation of data. It may take several days to construct a very generalized spreadsheet, but once you do you can crunch out data manipulations and calculations in a flash. For example,

=VLOOKUP(item_num,OFFSET(data!$B$4,0,0,data!$A$4+1,data!$A$2+1),HLOOKUP(H9,OFFSET(data!$B$1,0,0,3,data!$A$2+1),2,FALSE),FALSE)

This is a lookup function that that uses multiple nested lookups and returns a particular parameter that is found from using a HLOOKUP function within from an OFFSET value.

A much easier way of doing this function would have simply been to say

=VLOOKUP(item_num,$A$5:$R$72,24,FALSE)

But the issue then becomes when you start inserting columns or adding new items that extend beyond row 72, you'd have to manually go back and change the formulas. For example if you inserted a column at column 8, then the old column 24 would now be column 25 and your simplified VLOOKUP function would return the incorrect value. OFFSET allows your table range to be continually changing, since within the OFFSET function you can define how big your table is (how many rows and columns). The data table size can be easily defined with some dummy counters and MAX functions (such as cells having formulas like =if($b5<>"",A4+1,"") and then having a cell calculating the =MAX($A:$A) where one of the OFFSET parameters links to this cell.

In the more general case, the reference for which column to return is in itself a LOOKUP, so it will find the header of that column wherever it is in your lookup table, even if columns are inserted or deleted.

Hope this helps anyone who is wondering how to use VLOOKUP, OFFSET, and HLOOKUP in a combined way.

Anyone who has any ideas about my formatting question would be greatly appreciated.

Regards,
Stanley
Salt Lake City, UT

Thanks for the info; I wasn't sure if there was a trick or if the 2007 version of Excel was able to embed different formats within a formula. Guess not.