Run formula from different sheet

Question

Can you run formulas from different sheets? (not simply showing the formula as a string/static text)

Say I type

=Sheet1!A1

where in that cell I have a formula

=concatenate(ice&cream)

Here's what I want to see as a result

icecream

But instead of this, I get the same =concatenate(ice&cream) function as a text string.

In my particular case the concatenate function creates an IMPORTXML formula from these 3 columns:

Column 1 (B5):

IMPORTXML(url,"//*/div[2]/div[1]/div/div/div[

Column 2 (C5):

1

Column 3 (D5):

]/div/div/div[1]/h4/a/@href")

Column 4 (E5):

=CONCATENATE("=", B5, C5, D5)

And returns as a result:

=IMPORTXML(url,"//*/div[2]/div[1]/div/div/div[ 1 ]/div/div/div[1]/h4/a/@href")

which extracts a piece of data from http://website.com. I need the IMPORTXML formula to run and give me the piece of data, not return the same formula as static text.

NOTE: I use Google Sheets so the simpler the answer the better (i.e. no macros or other tools if possible) Thank you

Answer 1

The syntax for the Google Spreadsheet function IMPORTXML is

IMPORTXML(URL, xpath_query)

If you want to keep these parameters dynamic and feed them into the formula from other cells, then you will need something like

=Importxml(A1,A2&A3)

i.e. the string parameters are in the cells, but not the function name. Concatenate will always return a string, never a function. Concatenate is also always more to type than the simple & operator that does the same thing.