Playing with named ranges, I discovered it is possible to name a whole column (say "September") and use it in rows:
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.
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
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.