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.