Is there a way to use subscripts in my formulas?

[Neil Cash]

I use a lot of variables with subscripts. And would like the formatting to
follow the output when I put these in formulas.

i.e. =if(E18>0,"pr = 7","pr = 0")

I would like the "r" in pr be subscripted when it calculates the formula.
So far I've noticed that it removes all formatting when the formula
calculates. Is there a way around this?

Thanks in advance!

 

[Rick Rothstein]

No, you can't part of a cell's value be formatted differently from the rest
when using a formula. However, there is a VB solution that will do what you
want. However, you will need to lock down the range of cells the
functionality will apply to. In other words, when using VB event code, there
is no "copying down" type mechanism available when extending a functionality
across a range... you have to specify the entire range that could ever
possibly be involved before hand. So, tell us what the maximum range of
cells are where you would put your formula and how the formula is to apply
(E18 becomes E19 in next cell down, etc?) and we will try to write the event
code to do what you want. By the way, if the formula you posted is a
simplification for your actual formula, then you will need to tell us your
actual formula as the code will be specific written to duplicate what it
does.

 

[Neil Cash]

That example was certainly a simplification, and I need the formatting
throughout the entire worksheet. So I don't think I can narrow it down to
one range.

I'm a little familiar with VB and event programming. But not sure how you
could apply the formatting without specifying the specific cells, number of
instances, and basically taking the result and manually applying the
formatting. Seems tedious...

I guess if you can guide me with the right method for this code, I would
appreciate it.

Some things that should be noted:

-There isn't a set range of cells that need this formatting, it's random
throughout
-The formulas can be as complex as
="L x ( ½wLL + ½w"&IF(OR(AND(E23="√",E24="√"),E18=0),""," + ((wp x
a)(2L - a))/(2L²)")&" )"

Where I would need a few select letters of the output to be formatted with
the subscript.

-And I'm thinking it would need to be tied with the Dirty event, and don't
want it to bog down data entry with multiple programming runs.

I'm probably asking for the moon, but I would like to know how this is done
through VB.

Thanks again!

 

[Rick Rothstein]

I think VB can still handle what you want, although things might bog down
some if you have a wide-ranging number of formulas (not cell references for
a particular formula "shape", but rather, in the number of formula
"shapes"). Can you give me a list of the characters that would need to be
subscripted and those that would need to be superscripted; also, if there is
any other formatting you would want, tell me the characters and type of
formatting for those as well; and show me one or two formulas that you need
to handle. With them, I'll try to dummy-up some examples so you can see the
type of approach I am thinking about.

 

[Neil Cash]

Just for a little background, I'm working on a Masonry Lintel design
spreadsheet.

A good example of some of my formulas would be the design load.

I have the user specify the dead load and live load with the following
variables:
wDL and wLL (DL and LL should be subscripted)

Then I have a formula showing the total design load depending on whether or
not Arching Action occurs. If it does occur, then the dead load in this
instance drops from the equation:

=IF(OR(E23="X",E24="X"),"wDL + wlintel + wwall","wlintel + wwall")

For each of the variables I would like to have the first "w" be normal text,
and everything after that first "w" be subscripted. My other functions are
usually a case where if one thing is true use this formula, if not, use this
other formula. So being able to add the subscripts would be visually
appealing.

Thanks for the continued support!