How Can I Use a Column Number (not Letter) in an Excel Formula?

[netgroover]

I want to reference a range that is in column 25 and spans rows 100 to 200. I don't want to switch the mode to R1C1, I just need a way to describe this range in a formula. Seems easy but without creating a UDF to convert column Y to 25 I assume there is an easier way. Mark

[Ron Coderre]

Not sure exactly what you're looking to do....but
here's one way:

=INDEX(1:1048576,100,25):INDEX(1:1048576,200,25)

Is that something you can work with?

[netgroover]

Forgot to mention that I need to include the tabname, as ranges will vary according to the worksheet name, row and column. Mark

[Ron Coderre]

On Sheet1
A1: a tab name to reference......Sheet2
A2: a column number.................25
A3: a start row........................100
A4: an end row........................200

This formula returns a reference to the range components above

A5: =INDIRECT("'"&A1&"'!"&ADDRESS(A3,A2)&":"&ADDRESS(A4,A2))

In that example the formula references: 'Sheet2'!$Y$100:$Y$200

Is that something you can work with?

[netgroover]

Ron, thank you so much for your help, but I get a VALUE# error when I try that.

[Ron Coderre]

Yes, you do..
You're referencing a range via INDIRECT. Were you expecting the cell to display all 101 values?
If you change A3 to also equal 100, the formula will reference a single cell and you'll get a value instead of and error.
That formula is not an end unto itself. A structure like that would be typically be referenced by other formulas to return a single value.
For example, this formula would return the cell value of the 3rd cell in the referenced range:
=INDEX(INDIRECT("'"&A1&"'!"&ADDRESS(A3,A2)&":"&ADDRESS(A4,A2)),3)

Does that help?

[Beamernsw]

How about, with the column # in cell A1:

=INDIRECT("R100C"&A1&":R200C"&A1,FALSE)