Tuesday, May 31, 2011

yogi_Substitute Part Of A String With A Variable In ImportXML Formula

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com
Amoner said:
Substituting a part of the formula with the content of an adjacent cell
I am trying to modify my [code]=importXML("http://www.google.com/search?q=B1&num=100", "//a[@class='l']/@href")[/code] and "B1" is where formula should grab the content from... I have tried a multiple number of ways to fix the problem. But what I would want is that I modify the B1 cell and it refreshes the formula. Trying to simplify the process for my co-workers. Is this doable?

ignore [code] part
So the 2nd column is what the result should be like, but if you try substituting "Swiss Colony" with B1, it just searches for B1. Which makes sense because it all enclosed in "" , so I tried breaking it down into separate parts to make B1 an active element, I succeeded with that in D2 and the following. But it wont execute it, because google reads is an an outcome not an input...
ImportXML formula in cell B2 uses the string Swiss Colony
I have created a formula in cell C2 which uses the variable in cell B1 instead of the literal string Swiss Colony