Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Max function for row with named cells | Excel Worksheet Functions | |||
Using SUMIF Function with a named cell reference as value in CRITE | Excel Worksheet Functions | |||
inserting a named range into new cells based on a named cell | Excel Discussion (Misc queries) | |||
Defined Named cells lined to a Function Procedure | Excel Worksheet Functions | |||
how do you use named cells in the countif function | Excel Worksheet Functions |