How to access elements of a named range with Index or Offset w/o array spill

[OaklandJim]

I did see some similar posts but nothing seemed to give me the answer. I apologize if this has been answered (numerous times)...

I named a horizontal one row range with 26 cells. I tried to use Index and offset to access a specific cell. Got the #SPILL result which, I've come to learn indicates that excel thinks I am accessing an entire array rather than one specific element (cell) of the named range.

'=IF(bInclude, OFFSET(Item1_Target,rcChart3ColIndex-1,0), 0)
'=IF(bInclude, Index(Item1_Target,rcChart3ColIndex), 0)

What simple concept am I missing?

Jim

 

[OaklandJim]

Of course. I was using an incorrect cell index with the Index function so indeed my screwed up input to the formula caused the grief. Index function now seems to work as expected.

 

[pgc01]

Hi Jim

You say you have a horizontal named range, but in the Offset() function you are using a vertical offset.