Define Name dynamically to use in calculation of EBITDA

I don't see how what you're trying to do is going to help you with changing years. For any given column, EBITDA will always equal EBIT plus D&A.

When you years change, what becomes of the values associated with the year you just changed?

Your question is, unfortunately, confusing...

Clarification

Sorry, if it is confusing. I am in the midst of a financial model that I would like to associate names with so that I do not have to move back and forth between columns and tabs so much to try and find the exact cell that I need. Rather, I would like to name cells EBIT01, EBIT02, EBIT03, or NetInc01, NetInc02, NetInc03. I would like to do this as dynamically as possible rather than having to define each cell with such a manually intensive and time consuming process.

I would rather have the cells change their name based on the name of the row that I assign with their corresponding year. My model will be used for other financial statements with different year information. For example, my model contains 5 historical years of actual data and 5 years of historical proforma data for a total of 10 years. Next year, it will be the same, however, without changing the innerworkings of my model and keeping the 10 years, I would like EBIT01 to now become EBIT02. Regardless of the data, I would like the names to change.

I hope this helps. Thanks for looking and taking an interest.

Assuming your year is input in cell A7 (change the range accordingly) and your Depreciation & Amortization range is named "Dep_Am":


Hope this helps.

Thanks

Hey, thanks for that; I'm not sure exactly what to do with it though. I have input that formula in excel and haven't quite figured out what it is doing, or not doing. I have named the range B11:G11 ="EBIT" and B9:G9=Dep_Am as a test workbook. I then input years B7:G7 and named them "Year". The result is that of EBITDA, but it doesn't quite solve what I am looking for.

I would like to have a cell named EBITDA01 where the 2001 column and EBITDA row named ranges interesect. Furthermore, this named range needs to dynamically change with the year. So if 2001 becomes say, 2003, that same cell will reflect EBITDA03 now and no longer be named EBITDA01.

Hope that helps, and thanks again. I may be able to use that function you provided earlier elsewhere in my model.

Well, I assumed you wanted to input your year in a cell (A7 in my example) and return EBITDA based on that year. Try putting 2001 in cell A1, and put my formula in B1 (changing the reference from A7 to A1).

Let me know if this is what you need.

Thanks, but no joy

I got your formula to work; but it's not quite what I am looking for. The range isn't named EBITDA01. Let's start over and forget about calculating anything. Let's use the example of 'Revenue' I just need the following:

I need each cell for the corresponding year to be dynamically named with a row which I have named 'Revenue'. For example:

2001 2002 2003 2004
Revenue 50 75 80 100

I would like 2001 Revenue to be named Rev01 and 2002 to be named Rev02. So on and so forth. The only stipulation is that these years can change; so just because that cell is named Rev01 today, doesn't mean that tomorrow it won't be changed to Rev07 because the year could be changed to 2007 and I need it to be dynamic in that regard.

Hope that helps. Thanks for sticking with me on this one.

I'm not aware of how this can be done, but I've put in a distress call to the other MVP's. If it can be done, they'll know it!!

Sorry I'm not able to finish this off for you.

Heya Barrie, saw yer flare...

Barrie's right. You can't do it quite like you're thinking in that AFAIK you cannot have a specific cell have a "dynamic name" it flows in the other direction -- fixed name, dynamic address. I've only skimmed this since I'm getting ready to hit the hay for the night. But I think that you could make use of the INDEX() function. In your example of wanting to sum specific elements of each range, you can simply specify them like:<ul>[*]=INDEX(EBIT,3)+INDEX(DepAmo,3)[/list]Or, if you need to, you can make that hardcoded three based on something else like: column...<ul>[*]=INDEX(EBIT,COLUMN())+INDEX(DepAmo,COLUMN())[/list]Or you can use match to create it...<ul>[*]=INDEX(EBIT,MATCH($A$1,myYears))+INDEX(DepAmo,MATCH($A$1,myYears))[/list](I named the range for years myYears since "year" is a function and it's generally bad joojoo to create a named range that corresponds to a built-in function. <s>Also you indicate that you created a name with an ampersand. AFAIK that's not legal and would be rejected by Excel since the ampersand is the concatenation operator.</s> ^edit think I read that too quickly and it was only your description of the contents that had the ampersand and not the example name you cite. _/edit)

Hi McGu,

Take a look at this posting. I think you can use this to do almost exactly what you describe.

http://www.mrexcel.com/board2/viewtopic.php?t=230075&highlight=

Keep Excelling.

Damon