Using text from a cell as part of formula text

Hi,

Is there a way to use text from a cell as part of formula text in another cell?

For example:
in general, there is four sheets in the workbook (car, banana, apple and result).
Car, banana, and apple sheets have the same structure and data.

In the result sheet:
If A1 is a list and you can select "car!, banana!, apple!"
A2 is the "main formula". In this formula I would like to pick the sheet to be used from the list in A1, but use the rest of formula text as typed in the formula.

For example, in short: "=SUM(A1&B1:B100)". By changing the A1 results in different total. I have tried with CONCATENATE and & " " methods, but does not work. I also tried FORMULATEXT before CONCATENATE, but all these results in #VALUE!

Brg,
Thomas

You will need the |INDIRECT function: https://exceljet.net/formula/indirec...ifferent-sheet

If you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.