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  

Store formulas in Vlookups? (another try ;)



 
 
Thread Tools Display Modes
  #1  
Old April 17th, 2010, 12:04 AM posted to microsoft.public.excel.worksheet.functions
patbarb
external usenet poster
 
Posts: 6
Default Store formulas in Vlookups? (another try ;)

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...heet.functions
  #2  
Old April 17th, 2010, 12:50 AM posted to microsoft.public.excel.worksheet.functions
ker_01
external usenet poster
 
Posts: 87
Default Store formulas in Vlookups? (another try ;)

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...heet.functions
.

  #3  
Old April 17th, 2010, 01:13 AM posted to microsoft.public.excel.worksheet.functions
patbarb
external usenet poster
 
Posts: 6
Default Store formulas in Vlookups? (another try ;)

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 &nbsp, to add spacing between words? -patrick

---
frmsrcurl: http://msgroups.net/microsoft.public...ps-another-try
  #4  
Old April 17th, 2010, 01:18 AM posted to microsoft.public.excel.worksheet.functions
JLatham
external usenet poster
 
Posts: 1,896
Default Store formulas in Vlookups? (another try ;)

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...heet.functions
.

  #5  
Old April 17th, 2010, 01:26 AM posted to microsoft.public.excel.worksheet.functions
patbarb
external usenet poster
 
Posts: 6
Default Store formulas in Vlookups? (another try ;)

Thanks for the input, jLatham. I shall be looking at this later this eve. -)

---
frmsrcurl: http://msgroups.net/microsoft.public...ps-another-try
  #6  
Old April 17th, 2010, 03:05 AM posted to microsoft.public.excel.worksheet.functions
JLatham
external usenet poster
 
Posts: 1,896
Default Store formulas in Vlookups? (another try ;)

I hope it helps some. I'm not sure I have your whole picture, but I hope I
got enough of a glimpse of it to give something approaching a coherent
response. I'll try to keep an eye on this discussion to see if it
worked/helped or not. But it's tough to do without the notification of
response working in here!

"patbarb" wrote:

Thanks for the input, jLatham. I shall be looking at this later this eve. -)

---
frmsrcurl: http://msgroups.net/microsoft.public...ps-another-try
.

  #7  
Old April 17th, 2010, 06:12 AM posted to microsoft.public.excel.worksheet.functions
patbarb
external usenet poster
 
Posts: 6
Default Store formulas in Vlookups? (another try ;)

(testing - I just got blocked as a spammer! Ouch.

---
frmsrcurl: http://msgroups.net/microsoft.public...ps-another-try
 




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 01:50 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.