Define Name dynamically to use in calculation of EBITDA

[McGu]

If there is a row that has the year in each column:

2001 2002 2003 2004


and I label this row "Year";

I then have a row named "EBIT"

with values in the corresponding years

500 600 800 400

Dep&Am the same way:

100 200 150 260


Is there a way to name say:

EBITDA01 = EBIT01+DepAm01
EBITDA02 = EBIT02+DepAm02

My purpose in doing this is that the years will change frequently.


Basically, I just want to be able to name the rows and have the year "lookup" the corersponding row/value. Thanks for your help.

 

[Richard Schollar]

Damon

I never knew you could do that - frankly, I always wondered what "Accept labels in formulas" actually did (but, of course, never got off my lazy behind to find out!).

 

[McGu]

Perfect!

Gentlemen-

I have been throuroughly impressed by all responses to my inquiry. Thank you especially to Damon for the EXACT link that I needed. In addition, the bits written and the explanations provided earlier will be valuable as well. Again, thanks to all.

 

[Greg Truby]

OK - (a) Damon, thank you for that!

(b) Ditto what Richard Scholler wrote, I'd seen that tickbox in options but had never bothered to play with it. So thanks to Erik for the kick in the britches to make me play with it some...

And -- here's the fun/surprising part --

(c) I quite expected the wheels to fall right off this when I tried it with Year labels across the type like McGu has. After all you cannot name a range '2000' or '1984'. However when there is an intersect operator (the space) in play, Excel will properly utilize a numeric column (or row) label. i.e. SUM(2002) not gonna fly. But =2002 red * 7 will work or even =2003 blue +2000 orange

Oh... but it gets better...

Somehow Excel is built to accept year values from our era as valid column/row labels (yeah, I can't believe MS thought of this either). <ul>[*]Try using 2000 as a column label: workie, workie.[*]Try using 1901 as a column label: workie, workie.[*]Try using 1899 as a column/row label: kablooey, kaboomy![/list]Ya jus' never know which thread is gonna be a real learning opportunity...

 

[McGu]

Alright

Although my initial reaction was optimistic about the link and I thought that I understood what was going on, I have since realized that it doesn't quite get me to where I need to be. It is great to know that I can now intersect ranges to call up values. This is half the battle, and a good start to my issue. What I still don't have is a way to name the actual cell. I still have to recall that 'Revenue YR01" is in fact Rev01. I would like the ability to just type Rev01 and have it dynamically update to Rev02, say if I change the cell to '2002'.

On a side note, is there a way to name cells with '02' rather than 'YR02'. I was thinking of using Concantenate or 'Text' or something like this in the Define name dialog box, but it won't accpet it. Maybe I'm doing something wrong. Again, thanks for the help.

 

[Greg Truby]

Luke you've got a bit too much vaguery in there. "Change the cell to '2002'" to be specific. If you change the value in the label, Excel does update dependent formulae with the new reference. Note that you can use concatenation in the INDIRECT() function.

^edit However, upon testing INDIRECT does not appear to play very nice with the labels bit. Hmmm... _/edit

 

[Richard Schollar]

Ditto what Richard Scholler wrote...
~Trudy

Gramps you gotta keep at those brain exercises afore your mind goes completely and it won't jus' be words you having trouble with.

I liked your post though - interesting, interesting!

 

[McGu]

Sorry about that. I've included a spreadsheet for basic illustration purposes. What I would like to happen is in the report section, if I need Revenue for 2001 or 2006, I would simply like to type in cell C16 "=Rev01". Even more ideally, would be to select from a drop down list Revenue, EBIT, etc in cell A16 and have cells C16:E16 fill across based on the information in C15:E16. That would be ideal! However, with either of these options, I need the ability to have so that Rev01 could become Rev04 if 2004 was my first year rather than 2001.

Hope this helps.

Book1
	A	B	C	D	E
1	Scenario1
2			2001	2002	2003
3	Revenue		$500	$550	$600
4	Oper.Exp.		$275	$400	$700
5	NetIncome		$225	$150	($100)
6
7
8	Scenario2
9			2004	2005	2006
10	Revenue		$675	$880	$750
11	Oper.Exp.		$176	$222	$237
12	NetIncome		$499	$658	$513
13
14
15	Report		2001	2006
16	Revenue		$500	$750
Sheet1