Using named cells and CELL function
 

Hello

I am would like to reference a named cell in a formula
depending on what is shown in a third cell. To be more
specific, I have a series of typical areas called out at
the top of my spreadsheet. This can be seen in the
example below. I have manually named cells B2:B6 the
name of the area in A2:A6. (would love to know of a way
to name B2:B6 whatever name is in A2:A6, but that is not
my question in this posting:o) ) What I am trying to do
is multiply the given W/SF in B9:10 by the correct area to
come up with an estimate of the watts (W) used in that
area. I have used the CELL function with
info_type "Contents" to get it to return the text in the
correct cell. For example, =cell("contents", A9) returns
Hall, but it is not reference B4, the cell named Hall.
Therefore the formulas in D9:D11 are not working. Is it
possible to do what I am trying to do with the cell
function or should I try another approach?

A B C D
1 Areas
2 Complex 2000 SF
3 General 100 SF
4 Hall 100 SF
5 Kitchen 200 SF
6 Theatre 500 SF
7
8 Lighting W/SF W
9 Hall 2.25 SF =cell("Contents",A9)*B9
10 Kitchen 2 SF =cell("Contents",A10)*B10
11 Theatre 5 SF =cell("Contents",A11)*B11

Many thanks!
-Jay

Using named cells and CELL function
 

Try =INDIRECT(A9)

"Jay" wrote in message
...
Hello

I am would like to reference a named cell in a formula
depending on what is shown in a third cell. To be more
specific, I have a series of typical areas called out at
the top of my spreadsheet. This can be seen in the
example below. I have manually named cells B2:B6 the
name of the area in A2:A6. (would love to know of a way
to name B2:B6 whatever name is in A2:A6, but that is not
my question in this posting:o) ) What I am trying to do
is multiply the given W/SF in B9:10 by the correct area to
come up with an estimate of the watts (W) used in that
area. I have used the CELL function with
info_type "Contents" to get it to return the text in the
correct cell. For example, =cell("contents", A9) returns
Hall, but it is not reference B4, the cell named Hall.
Therefore the formulas in D9:D11 are not working. Is it
possible to do what I am trying to do with the cell
function or should I try another approach?

A B C D
1 Areas
2 Complex 2000 SF
3 General 100 SF
4 Hall 100 SF
5 Kitchen 200 SF
6 Theatre 500 SF
7
8 Lighting W/SF W
9 Hall 2.25 SF =cell("Contents",A9)*B9
10 Kitchen 2 SF =cell("Contents",A10)*B10
11 Theatre 5 SF =cell("Contents",A11)*B11

Many thanks!
-Jay