How do I refer to a specfic cell in a named range? ie Codes(1,2)

[Pontificateur]

I'm trying to refer to the named range "GLCodes" in a formula, as in the
following... =IF((RawHours!B2=836041), GLCodes(1,3), GLCodes(2,4))

When this formula is placed in a cell, I want the resulting value to reflect
what is in GLCodes row 1, column 3, etc. I know this is Excel 101, and it's
frustrating to not be able to make something this (apparently) simple work!

 

[Susan Ramlet]

Hi, Pontificateur,

I'd suggest posting to an Excel newsgroup, where you'll find more folks
familiar tih Excel formulas and code.

--
Susan Ramlet
MVP - Office

Please reply to the newsgroups where others may benefit.

I'm trying to refer to the named range "GLCodes" in a formula, as in the
following... =IF((RawHours!B2=836041), GLCodes(1,3), GLCodes(2,4))

When this formula is placed in a cell, I want the resulting value to reflect
what is in GLCodes row 1, column 3, etc. I know this is Excel 101, and it's
frustrating to not be able to make something this (apparently) simple

work!

 

[Pontificateur]

Excellent suggestion, Susan. Thank you. This posting to the "generic"
Office group was directed by the built-in Microsoft Help in Excel. A strong
case for individualism over big government...

 

[Susan Ramlet]

Oh, interesting! Thanks for the tip. I can make a suggestion to the Excel
folks.

 

[Keme]

I'm trying to refer to the named range "GLCodes" in a formula, as in the
following... =IF((RawHours!B2=836041), GLCodes(1,3), GLCodes(2,4))

When this formula is placed in a cell, I want the resulting value to reflect
what is in GLCodes row 1, column 3, etc. I know this is Excel 101, and it's
frustrating to not be able to make something this (apparently) simple work!

The function you need is named "forskyvning" in the norwegian version of
Excel. You'll find it in the "lookup" category in the formula wizard,
and I belive the english version names it "offset". It takes five arguments:
- Range
- Vertical offset (from the upper row in the range)
- Horizontal offset (from the left column in the range)
- Heighth (optional, defaults to heighth of the base range)
- Width (optional, defaults to the width of the base range)

Note that a formula must return a single value. Assuming that GLCodes
covers more than one cell, you must specify heighth and width as 1 in
order for this function to return a single value.
Note also that you don't specify the row/column number in the range, but
the offset from top/left. (Row 1 has an offset of 0 from the top.)

Your formula should be (assuming my guess on function name is correct):
=IF((RawHours!B2=836041);offset(GLCodes;0;2;1;1);offset(GLCodes;1;3;1;1))