patboule.com

Sometimes searching can be tough, when what you’re looking for is trapped inside the language fundamental to the thing on you are searching about. It’s true, sometimes you can’t see the forest for the trees.

A mental block I have with Excel is trying to remember what number a column translates to when your spreadsheet gets really, really wide. Like in the A->CG range. I know that A is 1, and AA is 27…after that things get sketchy.

Who cares? You need this info when you try and use an offset in a vlookup calculation:

=vlookup(some_cell,up_left$corner$:bot_right$corner$,OFFSET,false)

My initial fallback – the Google – would be to search something like:

excel column formula

which of course gives totally useless results – because the search terms themselves are fundamental to Excel: they are the atomic building blocks of Excel. I’d may as well have searched “excel, row, column”.

I’ve run into similar problems trying to search historical info on IP address allocation. Since every node connected to the internet has an IP address, so there’s alot of meaningless hits. I want info on the concept, not the data.

The whole point of this post was for me to document the solution to my column OFFSET problem, so that when I forget it next, I can Google it, and at least hit this page.

Solution 1: The Excel Column Number Formula:

=column()

Solution 2 The Table:

It’s like a formula, but for lazy people:

	Column Heading
	—	A-	B-	C-	D-
A	1	27	53	79	105
B	2	28	54	80	106
C	3	29	55	81	107
D	4	30	56	82	108
E	5	31	57	83	109
F	6	32	58	84	110
G	7	33	59	85	111
H	8	34	60	86	112
I	9	35	61	87	113
J	10	36	62	88	114
K	11	37	63	89	115
L	12	38	64	90	116
M	13	39	65	91	117
N	14	40	66	92	118
O	15	41	67	93	119
P	16	42	68	94	120
Q	17	43	69	95	121
R	18	44	70	96	122
S	19	45	71	97	123
T	20	46	72	98	124
U	21	47	73	99	125
V	22	48	74	100	126
W	23	49	75	101	127
X	24	50	76	102	128
Y	25	51	77	103	129
Z	26	52	78	104	130

This entry was posted on Wednesday, September 22nd, 2010 at 6:26 pm and is filed under Uncategorized. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.