A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Use Vlookup to store formulas?



 
 
Thread Tools Display Modes
  #1  
Old April 14th, 2010, 02:18 AM posted to microsoft.public.excel.worksheet.functions
patbarb
external usenet poster
 
Posts: 6
Default Use Vlookup to store formulas?

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/
  #2  
Old April 14th, 2010, 08:14 AM posted to microsoft.public.excel.worksheet.functions
Roger Govier[_8_]
external usenet poster
 
Posts: 338
Default Use Vlookup to store formulas?

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...eet.functions/

  #3  
Old April 14th, 2010, 08:15 AM posted to microsoft.public.excel.worksheet.functions
Roger Govier[_8_]
external usenet poster
 
Posts: 338
Default Use Vlookup to store formulas?

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...eet.functions/

  #4  
Old April 14th, 2010, 05:49 PM posted to microsoft.public.excel.worksheet.functions
patrick barbour
external usenet poster
 
Posts: 2
Default Use Vlookup to store formulas?

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...store-formulas
  #5  
Old April 15th, 2010, 03:44 PM posted to microsoft.public.excel.worksheet.functions
Steve Dunn
external usenet poster
 
Posts: 192
Default Use Vlookup to store formulas?

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" / wrote in message
...
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...store-formulas


  #6  
Old April 15th, 2010, 06:55 PM posted to microsoft.public.excel.worksheet.functions
patrick barbour
external usenet poster
 
Posts: 2
Default Use Vlookup to store formulas?

Thanks for the input Steve. I'll be able to try your idea later today and will report back. -)

---
frmsrcurl: http://msgroups.net/microsoft.public...store-formulas
  #7  
Old April 16th, 2010, 10:20 PM posted to microsoft.public.excel.worksheet.functions
patbarb
external usenet poster
 
Posts: 6
Default Use Vlookup to store formulas?

Well, I don't think that the Index/Match/Offset stuff above gets me where I want to go - but that was cool learning that Offset can create a vector on the fly for Match to use! (That is, take an existing array and reshape it to only be 1 column wide, like above, which is what Match wants.) I'll try another post and do a better job explaining. Thanks all! --patrick

---
frmsrcurl: http://msgroups.net/microsoft.public...store-formulas
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 06:04 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.