Dynamic Cell Name PLEASE help. Too much time spent on this

[McGu]

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.

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

 

[McGu]

It has been suggested to me that I look into using MATCH and INDEX to get the result I am looking for. Is this correct and if so can someone point me in the right direction? Thanks.

Much appreciated.

 

[PaddyD]

As I think you've been directed before, you're going about this the wrong way.

Rather than:

dynamically rename a cell that may move so that a static formula will always refer to it

excel is built to:

build a flexible formula that will find an item that moves

...given the data above, give us an example of what you are trying to achieve.

 

[tusharm]

Re: Dynamic Cell Name PLEASE help. Too much time spent on t

{snip}

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.
{snip}

Consider using a named formula in your graph. Then, you can use simply choose which row(s) and/or column(s) you want to graph and the graph will automatically adjust.

See
Dynamic Charts
http://www.tushar-mehta.com/excel/newsgroups/dynamic_charts/index.html
particularly examples 3 through 6. If I understand your problem correctly, you want to combine examples 4 and 6.

 

[McGu]

Thanks gentlemen, I'll look at the link and suggestions again.