Use Vlookup to store formulas?
in [Functions]
|
From: patbarb on 13 Apr 2010 21:18 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.excel.worksheet.functions/
From: Roger Govier on 14 Apr 2010 03:14 Hi Patrick I'm not sure that I am following your example but, wouldn't it be B1 in the second expression =Vlookup(A1,Lookup_MoistCondition,2)& Vlookup(B1,Lookup_ MoistCondition,3) -- Regards Roger Govier patbarb wrote: > 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.excel.worksheet.functions/
From: Roger Govier on 14 Apr 2010 03:15 Hi Patrick I'm not sure that I am following your example but, wouldn't it be B1 in the second expression =Vlookup(A1,Lookup_MoistCondition,2)& Vlookup(B1,Lookup_ MoistCondition,3) -- Regards Roger Govier patbarb wrote: > 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.excel.worksheet.functions/
From: patrick barbour on 14 Apr 2010 12:49 Thanks for trying to wade through this long post, Roger! -) I realize it's pretty durned confusing, but it wouldn't be B1 in the second Vlookup the way I'm doing it. The columns I need to pull from change every row(!). The idea is that I'm pulling all the formula parts based only on whether Wet or Dry is chosen in column A. (It's pretty clunky. ) I check on Wet/Dry and then go to the lookup table to see which column I should be grabbing first in the main worksheet. I then use that same Wet/Dry choice to go back to the lookup table and find out what the second column is that I should be grabbing from the main worksheet, etc. This is because depending on what the user chooses in column A, I could be pulling columns B, C, F and Q or maybe a completely different set, like C, F, L, S, U and Z! I guess what I'm saying is that in an ideal world I could just check on Wet/Dry and then do one single lookup to return the correct formula text, e.g., 'B1&C1&F1&Q1'. I could then turn this text into a formula after updating the row # to reflect the actual row I was on (with Offset, or whatever). Again, thanks. --patrick --- frmsrcurl: http://msgroups.net/microsoft.public.excel.worksheet.functions/Use-Vlookup-to-store-formulas
From: Steve Dunn on 15 Apr 2010 10:44 Hi Patrick, I'm guessing from the lack of responses that others are as confused as I am, but maybe this will help you. MATCH() will return a position in your lookup range, so you could use something like =MATCH(A1, OFFSET(Lookup_MoistCondition,,,,1), 0) in a helper column (say E in this example), then use INDEX() to retrieve the details from each respective column =INDEX(OFFSET(Lookup_MoistCondition,,2,1), E1) & INDEX(OFFSET(Lookup_MoistCondition,,3,1), E1) & INDEX(OFFSET(Lookup_MoistCondition,,6,1), E1) Now, the question is, can you define a method for us to select which columns should be chosen? If you can put it in words, we may be able to refine the formula further. HTH Steve D. "patrick barbour" <user(a)msgroups.net/> wrote in message news:Ot9N7J$2KHA.5820(a)TK2MSFTNGP06.phx.gbl... > Thanks for trying to wade through this long post, Roger! -) I realize > it's pretty durned confusing, but it wouldn't be B1 in the second Vlookup > the way I'm doing it. The columns I need to pull from change every row(!). > The idea is that I'm pulling all the formula parts based only on whether > Wet or Dry is chosen in column A. (It's pretty clunky. > ) > > > I check on Wet/Dry and then go to the lookup table to see which column I > should be grabbing first in the main worksheet. I then use that same > Wet/Dry choice to go back to the lookup table and find out what the second > column is that I should be grabbing from the main worksheet, etc. This is > because depending on what the user chooses in column A, I could be pulling > columns B, C, F and Q or maybe a completely different set, like C, F, L, > S, U and Z! > > I guess what I'm saying is that in an ideal world I could just check on > Wet/Dry and then do one single lookup to return the correct formula text, > e.g., 'B1&C1&F1&Q1'. I could then turn this text into a formula after > updating the row # to reflect the actual row I was on (with Offset, or > whatever). Again, thanks. --patrick > > > --- > frmsrcurl: > http://msgroups.net/microsoft.public.excel.worksheet.functions/Use-Vlookup-to-store-formulas
|
Next
|
Last
Pages: 1 2 Prev: Vlookup & relative cell reference? Next: Sumproduct with sumif over mutiple worksheets |