Hi, I'm trying to figure out if it's practical to use Vlookup to bring back complete formulas to a spreadsheet when you have a ridiculous number of formula possibilities for a given cell.

I want to create a varying formula in cell E1 based on the text entered in A1. In the example below, the user selects first the condition of the pet (wet or dry) in A1. The user may optionally also select a name (Fluffy or Fido) in B1. Cells C1 and D1 simply say Scram and Welcome, respectively.

Row1, cells A-E - Wet/Dry Fluffy/Fido Scram Welcome (formula)

The idea is that if the user picks Wet, then the formula automatically places "Scram (pet name selected)" in cell E1. If Dry is picked, then it says "Welcome (pet name selected)". Pretty easy to do for a small formula just nesting a couple of IFs:

=IF(A1="Wet",D1&B1,IF(A1="Dry",C1&B1,""))

In reality, I actually have about 40 choices in the dropdown (=40 different formulas!) so I got excited when I had the idea of a Vlookup that could simply retrieve the correct formula based on the item selected. This works great for the first line, and when I drag down the formula it can easily keep sending the correct Wet/Dry choice from the dropdown (A1, A2, A3, ...), like so:

=Vlookup(A1,Lookup_MoistCondition,2)& Vlookup(A1,Lookup_ MoistCondition,3)&..., etc., to bring back as many lookup table columns as are needed for the complete formula

However, since the lookup table has no direct connection to the other rows back in the main spreadsheet, it doesn't realize that the user has entered a different pet name in B2, B3, B4... and it keeps thinking that the pet selected in B1 is always the right one.

Anybody got ideas on how to do this or if there's some other way to store formulas to keep using over and over again?

Thanks!
Patrick


---
frmsrcurl: http://msgroups.net/microsoft.public...eet.functions/