4

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.

1

1 Answer 1

2

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.

1
  • Or =SUM(INDIRECT(X1))*C1
    – Zaz
    Sep 13, 2016 at 12:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.