Search code examples
functionvalidationgoogle-sheetsreference

Google sheet - using a value from a function as a reference to another cell

  1. In google sheet i have created a formula =CONCATENATE("=";MID((FORMULATEXT(A2));2;6);"P3").

  2. This formula takes a refernce value from cell A2 (reference to a cell E2 in sheet 2) and combines part of it (using MID function) with a string "P3" which is a cell from sheet2. The resulting value after executing the formula is "='P01':P3" in cell D2 of sheet 1

The problem is that value "='P01':P3" is presented in cell D2 as text, but I want it to be a reference to cell P3 in another sheet.

How can i tell google sheets to use the result of a formula as a reference and not present it as plain text.

  1. What I want to accomplish is to auto-populate some cells in Sheet 1 with values from Sheet 2.

For example, sheet 1 has columns A1 - name
D1 - start date

Sheet 2 have cells
E2 - Project name
P3 - start date

So when I reference A1 of Sheet 1 to E2 in Sheet 2 the formula in cell D2 from sheet 1 will use MID function to extract the name of the sheet and then use CONCATENATE to add the cell number (in this case P3) to be able to get the value from cell P3 in Sheet 2 (the start date).

I have tried with INDIRECT function but with no success.

this is the link to google sheet https://docs.google.com/spreadsheets/d/1PUxxiaAIROPG8LMO-goeL-BIklpH0y6y8lqHkjtus2E/edit?usp=sharing

Thanks

I have tried to find an answer on multiple Google Docs forums and explored various Google functions but with no success

Solution

  • You may try:

    =indirect(CONCATENATE(MID((FORMULATEXT(A2));2;6);"P3"))