Reference column by number

L

Legacy 5596

Guest
Ok, imagine Excel didn't have column letters, and you simply had x,y
x= column, y=row.
Now, I know the column I want is column 7 (which is column G)and that I want the data from rows 3 thru 62.
Normally I could do G3:G62
But using the premise that I only know the number of the column, how can I do the same thing as G3:G62?
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
Howdy Roderick, welcome to the board (from a Hoosier Alum).

Try using the cells functionality. Here's and example:<pre>
Sub cls()
MsgBox Range(Cells(3, 7), Cells(62, 7)).Address(False, False)
End Sub</pre>

The columns and rows are reversed, rowindex then column index numbers.

_________________
Cheers,<font size=+2><font color="red"> Nate<font color="blue"> Oliver</font></font></font>
drink.gif

This message was edited by NateO on 2002-07-05 12:33
 
Upvote 0
Ack! :biggrin:

Well, I'm sure there's something better out there but here's an example:

=SUM(INDIRECT(ADDRESS(3,7)):INDIRECT(ADDRESS(62,7)))

There is a third paramter to address describing the reference style (e.g., absolute), it's not pertinent in the example above.

Gotta get my head out of the VBE :biggrin:. That's twice in two days I've done that.

Edit: You can replace the numbers w/ cell references.

_________________
Cheers,<font size=+2><font color="red"> Nate<font color="blue"> Oliver</font></font></font>
drink.gif

This message was edited by NateO on 2002-07-05 12:49
 
Upvote 0
No VBA required. Click on Tools/Options/General and check the box for "R1C1 reference style". This will change your column headings from letters to numbers. Then, if you have, for example, numbers in rows 10 thru 13 in column 7, you can use the following formula to, for instance, find their sum:

=SUM(r10c7:r13c7) where "r10" is row 10 and "c7" is column 7, etc.

this is the equivilent of =SUM(G10:G13) in the normal "letter" column mode.
 
Upvote 0
Great! almost there!!! :) -- one more question.
I'm trying to access another sheet,
Normally =Sheet2!G$3:G$62 would work just fine, How do I make it access using your code?

thanks again! -- Hoosier are we!
 
Upvote 0
Continuing with my bear of an example, perhaps Barry can expand on his, it could look like:

=SUM(INDIRECT(ADDRESS(3,7,,,"sheet2")):INDIRECT(ADDRESS(62,7,,,"sheet2")))
 
Upvote 0
Using my method, placing the following formula in cell A1 (or cell r1c1) in sheet 1 will give the sum of numbers in sheet 2, row 3/column 7 to row 61/column 7 (G3 to G61):

=SUM(Sheet2!r3c7:r61c7)

Incidentally, you don't even have to change the worksheet to display column headers as numbers. The references "G3" and "r3c7" are interchangeable and give same result no matter how screen is displayed. Changing column headings is only for ease in seeing which column is which when selecting r1c1 method.
 
Upvote 0
Thanks guys, I used NateO's code and here is what it looks like totally implemented -- thanks again!!!
----
=IF(ROUNDUP(INDEX(INDIRECT(ADDRESS(3,MATCH(I4,opspercalls_aban!A$2:K$2,0),,,"opspercalls_aban")):INDIRECT(ADDRESS(62,MATCH(I4,opspercalls_aban!A$2:K$2,0),,,"opspercalls_aban")),MATCH(IF(CEILING(calls!H3, 100)<calls!H3+50, CEILING(calls!H3,100), CEILING(calls!H3, 100)-100),opspercalls_aban!A$3:A$62)),0)>F$1, F$1,ROUNDUP(INDEX(INDIRECT(ADDRESS(3,MATCH(I4,opspercalls_aban!A$2:K$2,0),,,"opspercalls_aban")):INDIRECT(ADDRESS(62,MATCH(I4,opspercalls_aban!A$2:K$2,0),,,"opspercalls_aban")),MATCH(IF(CEILING(calls!H3, 100)<calls!H3+50, CEILING(calls!H3,100), CEILING(calls!H3, 100)-100),opspercalls_aban!A$3:A$62)),0))
 
Upvote 0
You're welcome Roderick.

Speaking of bears...So this is funcitonal eh? Glad you're on top of the rest. :biggrin:
 
Upvote 0

Forum statistics

Threads
1,213,563
Messages
6,114,332
Members
448,566
Latest member
Nickdozaj

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top