Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

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.

share|improve this question
    
Can you share a doc with us? –  Jacob Jan Tuinstra Jan 25 '13 at 21:13
add comment

1 Answer 1

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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