How to use INDEX and a column number or reference

**skysurfer** · 05-15-2010, 03:53 PM

Hi,

Is there a way to substitute a column letter in a formula, say the letter "C" for column C, with a reference to a cell containing that column letter?

Right now, I have the following formula:

=C17:INDEX(C:C,COUNTA(C:C))

I would like to link those C's to a cell containing either a number or a letter.

What I am trying to do is create a dynamic range with a column that may move left or right. Sometimes the dynamic range will be column C, sometimes another column. I am also trying to NOT use volatile function OFFSET which would work but slow things down.

Thanks,

Lawrence

**contaminated** · 05-15-2010, 04:28 PM

Like this
=INDEX(INDIRECT(A1&":"&A1),COUNTA(INDIRECT(A1&":"&A1)))
Where A1 contains "C" letter...
HTH xD

**DonkeyOte** · 05-15-2010, 04:54 PM

Originally Posted by skysurfer

I would like to link those C's to a cell containing either a number or a letter....

...What I am trying to do is create a dynamic range with a column that may move left or right. Sometimes the dynamic range will be column C, sometimes another column. I am also trying to NOT use volatile function OFFSET which would work but slow things down.

In which case use a Number.

=INDEX($17:$17,yournumber):INDEX($1:$65536,COUNTA(INDEX($1:$65536,0,yournumber),yournumber)

(User of letter would necessitate use of INDIRECT which, like OFFSET, is Volatile)

**skysurfer** · 05-15-2010, 04:59 PM

DonkeyOte,

Thanks! Will using $65536 in the formula create a problem going from Excel 2003 to 2007 or 2010?

Lawrence

**DonkeyOte** · 05-15-2010, 05:06 PM

If you use more then 65536 then yes.

**skysurfer** · 05-15-2010, 05:15 PM

Does that mean the $65536 can not be replaced by a cell reference containing a number or the COUNTA function?

Lawrence

**teylyn** · 05-16-2010, 06:49 AM

Using more than 65536 as a row reference will only be a problem in XL2003, because that version can only handle 65536 rows. In 2007 and later, a number greater than 65536 is fine, if you use the native xlsx format and not the 2003 compatible xls file format.