# How to use a named column in a calculation based on a cell value in Google Spreadsheet

Playing with named ranges, I discovered it is possible to name a whole column (say "September") and use it in rows:

``````=September*K4
``````

What I'm trying to achieve now is that the value of the column ("September") comes out as a variable on another named cell ("month").

I've seen that the formula `INDIRECT` is used to reference a cell, but calculations don't work if I use that.

So:

``````Column B named September
D10..  = "=September*C10  # works with its corresponding row number
``````

works fine but I would like to:

``````Column B named September
X1 = "September"
D1..  = "=INDIRECT(X1)*C1  # only works with the value of B1 first row
``````

EDIT

To clarify my question, imagine 3 months—each on a column—with a protected named range each (January, February, March). One cell (i.e. called `month_selection`, elsewhere or on another sheet) where I set a text value corresponding to the column I want to use in the calculation (i.e. "January").

By changing the month on `month_selector`, all row calculations on Sheet1 use that month as the column value. So it will automatically choose A18, B18 or C18 if column A was called January, column B if that was named February, etc.

I thought I had it using `COLUMN(INDIRECT(cell_with_name_of_colum_range)` but just realized it's not.

-
Can you share a doc with us? – Jacob Jan Tuinstra Jan 25 '13 at 21:13

## 1 Answer

Your problem comes not from using named ranges, but that the multiplication `=INDIRECT(X1)*C1` only uses the first row. `=INDIRECT("B1:B")*C1` gives the same result.

I'm not sure what you're trying to do, but if you want a list of values, you could wrap the expression in ArrayFormula: `=ARRAYFORMULA(INDIRECT(X1)*C1)`. This will fill column `D` with values from column `B`, multiplied by `C1`.

If you instead want the sum of all values from September, multiplied by `C1`, you would do something like: `=SUM(INDIRECT(X1)*C1)`.

I've cooked up an example spreadsheet here.

-