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.
allows you to format text characters having different font styles and sizes through the data entry bar.
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
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,
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
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
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.
Salt Lake City, UT