CODEWITHSUNDEEP
excelexcel-formulareplaceexcel-tables
Razero
Razero

Reputation: 341

Reference a table in formula with table name in a cell

Let's say that there is a excel sheet with a table (Table1) and a cell (C1) in which the table name is written (as text).

Can I make a formula that uses notation for using values from the table (e.g. Table1[Column1]), but instead of hardcoding the table name in the formula somehow reference the cell with the table name? Or some other way to make dynamic references to tables? Or maybe a simple find/replace or regex that can modify formulas?

Here's a simple example:

enter image description here

So here for example I'd like to make the formula in F3 so it's easy to modify so that if I want to count the odd numbers in some other table (which has a column named Column1) I can just simply modify the table name.

Note: This is a simplified example, in reality I have multiple complex formulas in which it would take a long time to replace the hardcoded names.

Upvotes: 0

Views: 2728

Answers (1)

user11222393
user11222393

Reputation: 5471

=SUMPRODUCT(--((MOD(INDIRECT(C1&"["&E4&"]"),2)=1)))

enter image description here

You should take a look at INDIRECT: indirect

But keep in mind that your workbook might become very slow if you overuse it.

Upvotes: 2

Related Questions