Store formulas in Vlookups? (another try ;)

[patbarb]

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 spreadshee
A
Dog ="="&Vlookup(A1,lookup_table,2

lookup_tabl
A
Dog C1&D
Log D
Bog Q

The result of this Vlookup is the text value =C1&D1, rather than the actual concatenated values of cells C1 and D1.

Thanks
patric

 

[ker_01]

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]

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

 

[JLatham]

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]

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

 

[JLatham]

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]

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