Use named range variable name dynamically in Google sheets

Question

I am new to Google sheets. I have one sheet with multiple named ranges. Each range is named with "company_{{ id }}" e.g. "company_snapchat".

I have list of company ids as follows:

|---------------------|------------------|
|      Company ID     |  Sum of Costs    |
|---------------------|------------------|
|      snapchat       |    <formula>     |
|---------------------|------------------|
|      facebook       |    <formula>     |
|---------------------|------------------|

In the formula I want to reference its named range as "company_"&a2. However it doesn't work.

does this =INDIRECT("company_"&A2) paste to the <formula> cell answer your question? or automatically create named range when new data added, might need to use apps-script (macro) — zummon
Hello AndroidDev, does @zummontt's suggestion work for your situation? If not, would you mind providing more details about your issue? Cheers! — ale13
Yes thanks a lot. Would you please add an answer to be approved? — AndroidDev

zummon zummon · Accepted Answer · 2020-04-24T05:16:48

Use INDIRECT() to refer value in a cell as cell reference

=INDIRECT("company_"&A2)

Normally we work with A1 or R1C1 but also work with named range too

Use named range variable name dynamically in Google sheets

1 Answers