My best example of using the indirect function is in selecting the type of
wood beam I want to design. This example is much better than apples and
trees that Microsoft uses. I often design wood beams of different
properties; sawn beams, Glu-Lam beams (GLB) and Parallel Strand Laminated
(PSL) beams.
I first set up a validation list to select the type of beam (Sawn, GLB or
PSL). Whichever I choose, the options for the different sizes change based
on my selection. In other words, if I want to design a sawn beam, I have a
list consisting of 2x4, 4x8 or 6x12. If I want to design a GLB, my list
consists of 3-1/8"x12", 5-1/8"x18" or 6-3/4"x24". If I want to design a PSL
beam, my list would change to 3-1/2"x11-1/4", 5-1/2"x14" or 7"x20". From
there, you could have simple lookup commands to fetch section properties
such as the beam width and beam depth (among other things).
This procedure (at least the way I do it) requires named ranges. To assist
in the visualization, I've included a sample. Note: there are no VBA scripts
in this example so don't worry about a virus.
Have fun!
T. William (Bill) Allen, S.E.
ALLEN DESIGNS
Consulting Structural Engineers