Store formulas in Vlookups? (another try ;)
in [Functions]
|
Prev: Date question
Next: Font by way of function?
From: patbarb on 16 Apr 2010 19:04 Is there a way to store formulas as text in a Vlookup table and then retrieve and activate them? For example, below I do a lookup on "Dog" (A1), return the text C1&D1 from the table and slap an equal sign onto the front of it. This does not magically activate the text into a formula, unfortunately. main spreadsheet A B Dog ="="&Vlookup(A1,lookup_table,2) lookup_table A B Dog C1&D1 Log D1 Bog Q1 The result of this Vlookup is the text value =C1&D1, rather than the actual concatenated values of cells C1 and D1. Thanks! patrick --- frmsrcurl: http://msgroups.net/microsoft.public.excel.worksheet.functions
From: ker_01 on 16 Apr 2010 19:50 Look in the helpfile for the function "Indirect" and you should be able to accomplish this. =A1 is the same as =indirect("A1") or even =indirect("A" & "1") so while I don't fully understand your example, wherever you are getting some formula returned as a text string, you just need to wrap that result (not the parent formula, just the returned result) in an indirect statement. HTH, Keith "patbarb" wrote: > Is there a way to store formulas as text in a Vlookup table and then retrieve and activate them? For example, below I do a lookup on "Dog" (A1), return the text C1&D1 from the table and slap an equal sign onto the front of it. This does not magically activate the text into a formula, unfortunately. > > main spreadsheet > A B > Dog ="="&Vlookup(A1,lookup_table,2) > > > lookup_table > A B > Dog C1&D1 > Log D1 > Bog Q1 > > The result of this Vlookup is the text value =C1&D1, rather than the actual concatenated values of cells C1 and D1. > > Thanks! > patrick > > > > --- > frmsrcurl: http://msgroups.net/microsoft.public.excel.worksheet.functions > . >
From: patbarb on 16 Apr 2010 20:13 Aargh, I can see why you don't understand my example too well - the spaces I used for spacing have all been stripped out! Is it possible to throw in HTML, like  , to add spacing between words? -patrick --- frmsrcurl: http://msgroups.net/microsoft.public.excel.worksheet.functions/Store-formulas-in-Vlookups-another-try
From: JLatham on 16 Apr 2010 20:18 I see that you've already tried, and found that your attempt in the main sheet simply returned a text representation of a formula without evaluating it. That's the way & works pretty much. If you take what ker_01 offered and carry it out some, you'll find that you can use INDIRECT to provide the 'address' part of a formula. And aren't you making this hard on yourself? What down in lookup table, you simply had this in B2: =C1 & D1 which would be the concatenation of C1 and D1, then A B Dog =VLookup(A1,lookup_table,2,False) would return what you want. But to carry this a little further. Lets say you have a lookup table like this: A E1:E6 B E1:E10 C F1:F5 D G1:G6 somewhere else you could have a setup like this: A B 1 C =SUM(INDIRECT(VLOOKUP(A1,lookup_table,2,FALSE))) that becomes the same as =SUM(F1:F5) perhaps that helps? Or back to your original issue, if you had a 3 column lookup table like this A B C Dog D1 E1 Log F1 G1 Bog H1 I1 You could contatenate via indirect like this: = Indirect(VLookup(A1,lookup_table,2,False)) & Indirect(VLookup(A1,lookup_table,3,False)) Where you only have a single cell to truly concatenate, just make the column C entry in the lookup table point to an empty cell. "patbarb" wrote: > Is there a way to store formulas as text in a Vlookup table and then retrieve and activate them? For example, below I do a lookup on "Dog" (A1), return the text C1&D1 from the table and slap an equal sign onto the front of it. This does not magically activate the text into a formula, unfortunately. > > main spreadsheet > A B > Dog ="="&Vlookup(A1,lookup_table,2) > > > lookup_table > A B > Dog C1&D1 > Log D1 > Bog Q1 > > The result of this Vlookup is the text value =C1&D1, rather than the actual concatenated values of cells C1 and D1. > > Thanks! > patrick > > > > --- > frmsrcurl: http://msgroups.net/microsoft.public.excel.worksheet.functions > . >
From: patbarb on 16 Apr 2010 20:26
Thanks for the input, jLatham. I shall be looking at this later this eve. -) --- frmsrcurl: http://msgroups.net/microsoft.public.excel.worksheet.functions/Store-formulas-in-Vlookups-another-try |