I have asked this before and each time I am refining my question, so here it goes:
Is there a way to dynamically name a cell based on the column and row labels?
I know that I can refer to a cell based on its row and column labels with a space in between, but how can I actually name the cell?
In the following chart, I have a Row 5 labeled 'Revenue' and Columns B, D, F, and H labeled 12/31/2002, 12/31/2003, 12/31/2004, 12/31/2005. (This is acceptable for labels, but they will not work with INDIRECT apparantly).
I would like to name the cells B5, D5, F5 and H5; 'Revenue2002', 'Revenue2003', 'Revenue2004', and 'Revenue2005'.
Each cell must be dynamic in that if the column label changes from '12/31/2002' to '12/31/2009', then the cell name must change from 'Revenue2002' to 'Revenue2009' also.
I do not have to use dates for the column labels, but it sure would make things much easier if I could.
There is a second part to this question, but we'll start here. The whole purpose of this is to be able to recall figures from the sheet named 'Model' to graph 'Revenue' over the selected periods.
Is there a way to dynamically name a cell based on the column and row labels?
I know that I can refer to a cell based on its row and column labels with a space in between, but how can I actually name the cell?
In the following chart, I have a Row 5 labeled 'Revenue' and Columns B, D, F, and H labeled 12/31/2002, 12/31/2003, 12/31/2004, 12/31/2005. (This is acceptable for labels, but they will not work with INDIRECT apparantly).
I would like to name the cells B5, D5, F5 and H5; 'Revenue2002', 'Revenue2003', 'Revenue2004', and 'Revenue2005'.
Each cell must be dynamic in that if the column label changes from '12/31/2002' to '12/31/2009', then the cell name must change from 'Revenue2002' to 'Revenue2009' also.
I do not have to use dates for the column labels, but it sure would make things much easier if I could.
There is a second part to this question, but we'll start here. The whole purpose of this is to be able to recall figures from the sheet named 'Model' to graph 'Revenue' over the selected periods.
FinancialModel V.10a experiment label.xls | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | |||
1 | IncomeStatementforXYZCompany | |||||||||
2 | Millionsof$ | |||||||||
3 | Historical | |||||||||
4 | 2002 | (12) | 2003 | (12) | 2004 | (12) | 2005 | |||
5 | Revenue | $1,235 | 100.0% | $1,252 | 100.0% | $1,300 | 100.0% | $1,334 | ||
6 | CostofSales | $679 | 55.0% | $659 | 52.6% | $681 | 52.4% | $667 | ||
7 | GrossOperatingIncome | $556 | 45.0% | $593 | 47.4% | $619 | 47.6% | $667 | ||
8 | ||||||||||
9 | Selling,General&Admn.Expenses | $340 | 27.5% | $349 | 27.9% | $351 | 27.0% | $373 | ||
10 | DepreciationandAmortization | $48 | 3.8% | $52 | 4.2% | $56 | 4.3% | $75 | ||
11 | OtherNet(Income)/Expenses | ($12) | -1.0% | ($8) | -0.6% | ($7) | -0.5% | ($8) | ||
12 | EBIT | $180 | 14.6% | $200 | 16.0% | $219 | 16.8% | $227 | ||
13 | ||||||||||
14 | Interest(Income) | ($1) | -0.1% | ($1) | -0.1% | ($2) | -0.1% | ($2) | ||
15 | InterestExpense | $16 | 1.3% | $15 | 1.2% | $21 | 1.6% | $24 | ||
16 | Pre-TaxIncome | $166 | 13.4% | $186 | 14.9% | $200 | 15.4% | $205 | ||
17 | ||||||||||
18 | IncomeTaxes | $57 | 4.6% | $64 | 5.1% | $68 | 5.2% | $73 | ||
19 | NetIncome | $109 | 8.8% | $122 | 9.7% | $133 | 10.2% | $133 | ||
20 | ||||||||||
Model |