Range names in financial projections; to enable retrieving data from a specific period (column)

[L Mehl]

Hello --

In projecting cash flows over time, I want to name the summary rows
(revenue, expenses, capital expenditures, etc.) and columns (year 0, year 1,
....) so that I can easily get each row's value for a specific period, for
further processing.

I have seen the method where columns and rows are named, and the
intersection of a column and row is named using their combination, separated
by a space (as the intersection operator).

Can anyone recommend other methods which have worked for them?

Thanks for any help.

Larry Mehl
mehl_at_cyvest.com

 

[Alan]

Hello --

In projecting cash flows over time, I want to name the summary rows
(revenue, expenses, capital expenditures, etc.) and columns (year 0,
year 1, ...) so that I can easily get each row's value for a
specific period, for further processing.

I have seen the method where columns and rows are named, and the
intersection of a column and row is named using their combination,
separated by a space (as the intersection operator).

Can anyone recommend other methods which have worked for them?

Thanks for any help.

Larry Mehl
mehl_at_cyvest.com

Hi,

Intersection works well, but I am guessing you have some reason for
not wanting to use that?

The other alternative is perhaps MATCH and OFFSET.

Alan.

 

[Andy Brown]

I have seen the method where columns and rows are named, and the

intersection of a column and row is named using their combination, separated
by a space (as the intersection operator).

Yes. With Year0, Year1, Year2 in B11 ; and Revenue, Expenses, Capital in
A2:A4.

Select A14 and Insert (menu) -- Name -- Create.

Make sure "Top Row" *and* "Left Column" are checked, then click OK.

Then you can use e.g.

=Expenses Year1

to get the intersection value.

Rgds,
Andy

 

[Andy Brown]

Can anyone recommend other methods which have worked for them?

Oops, mis-read ; apols.

A method along the lines Alan hints at can be found here
http://j-walk.com/ss/excel/usertips/tip020.htm

Rgds,
Andy

 

[L Mehl]

Alana and Andy,

Thanks.

I have heard that intersection is unreliable, so I will explore the INDEX
and MATCH approach.

Larry