excel cell address

December 27, 2009 at 15:08:10
Specs: Windows XP
Hi:

I have an array of numbers in cells from A1 to G1.
I have a cell A2 that generated the column address from A to G.
I have cell B2 were I want a number from the array A1:G1 to go to based on cell A2.

I tried =(A2)1 and many others.
I tried Googleing this but do not know what to ask for.

Thanks,
fg


See More: excel cell address

Report •


#1
December 27, 2009 at 16:15:30
Hi,

If A2 has a letter from A to G, then in cell B2 use this:

=INDIRECT(A2&"1")

If A2 was C then this formula returns the contents of cell C1

Hope this was what you wanted.

Regards


Report •

#2
December 27, 2009 at 16:15:39
re: I have a cell A2 that generated the column address from A to G.

I'm not sure what you mean by "generated the column address", but is this what you are looking for?

Let's say you have this:

	
         A	B	C	D	E	G
1	20	30	40	50	60	70
2	 4					

In B2 try this:

=OFFSET(A1,0,A2-1)

In my example, this will return the value in D1 which is 50.

=OFFSET(reference,rows,cols,height,width)

reference -> A1
rows -> 0
cols -> 4-1 = 3
height -> n/a
width -> n/a

OFFSET from A1 zero rows, 3 columns



Report •

#3
December 27, 2009 at 16:56:34
Humar:
That worked, thanks

Durby:
A2 has a function that generates the col. add. and B2 is were the array number is placed.
Thanks

Is there a good ref/book on net for this typr of info [ did not know about the & but did know about the indirect()]?
Thanks
gillmre


Report •

Related Solutions

#4
December 27, 2009 at 17:08:39
My question stemmed from the fact that columns don't have "addresses". They have numbers [A=1, B=2, etc) and of course, they have their letter designations.

Cells have addresses, ranges have addresses, and to a certain extent, shapes have addresses.

Columns and rows do not.


Report •

#5
December 27, 2009 at 17:19:23
Derby:

That is clear col. number [letterl] thanks lost to learn.
gillmre


Report •

#6
December 28, 2009 at 08:20:39
Actually, I mispoke.

I was thinking in terms of what you are doing - pulling a value from a single cell within Excel.

Columns and Rows do in fact have addresses.

Entire Columns and/or Rows, that is.

If you run this code, you'll see what I mean:

Sub WhatAddress()
'Show cell addresses
  MsgBox Range("E6").Address
  MsgBox Cells(6, 5).Address
  MsgBox [E6].Address
'Show Column Addresses
  MsgBox Columns(5).Address
  MsgBox Columns("E").Address
'Show Row Address
  MsgBox Rows(6).Address
  MsgBox Rows("6").Address
End Sub


Report •

#7
December 28, 2009 at 08:50:00
Derby:
I do not know how th run the program, but i read through it and see what it does. Thanks for the corection. Now I know what to call the row and col add's, was not sure.
gillmre

Report •

#8
December 28, 2009 at 09:22:55
Right click a sheet tab and paste the code into the VBA editor.

Then click anywhere within the code so the cursor is flashing within it.

Next click the little green arrow in the top toolbar.


Report •

#9
December 28, 2009 at 09:45:38
right click does not give me a VB ed. and i looked for it found one under tools and macro but could not past [gray screen there].

Report •

#10
December 28, 2009 at 09:56:01
Right click a sheet tab and choose View Code

Paste the code into the VBA editor


Report •

#11
December 28, 2009 at 10:26:21
ok so far clickd the green => now what? do not know how to run vb on a cell, or col or row add.

Report •

#12
December 28, 2009 at 10:43:12
If you pasted the code into the VBA editor, placed your cursor somewhere within the code and clicked the green arrow, you should see a series of Message Boxes showing the address of the cell in my example (E6) as well as the address for Column E and the address for Row 6.

Don't trouble yourself too much, it was just a way to show that columns and rows technically have addresses.

The address of Column A (or Column 1) is $A:$A, B would be $B:$B, etc.

The address of Row 1 would be $1:$1, 2 would be $2:$2, etc.


Report •

#13
December 28, 2009 at 10:52:37
when i copied the program and clicked the green => disapered from the tool bar. It moved to a small box and now that is gone

Report •

#14
December 28, 2009 at 10:56:29
Forget it.

It's not worth the trouble.


Report •

#15
December 28, 2009 at 11:15:00
Derby:
Thanks for your time.
gillmre

Report •

#16
December 28, 2009 at 11:28:03
Hi,

I can't point you to a good book, but I do find the function list useful...

At top left above cell A1is the box that shows the currently selected cell or cells. When you enter the = sign into a cell, this box shows a function and a small drop-down arrow at the right.
Click the drop-down arrow and from a longer list of recently used functions, choose the last entry 'More Functions...'.
In the box that opens, either type in a description of what function you want or use the Category box to get a list of related functions.

Either of these can help you find a function that might do what you want. Selecting a function gives a brief description, and then clicking on the 'Help on this function' hyper-link brings up Excel's help.

Regards


Report •

#17
December 28, 2009 at 11:34:53
Try here:

http://www.ozgrid.com/free-excel.htm


Report •

#18
December 28, 2009 at 12:24:42
Humar:
I have been using ƒx to get there. What u helped me with i could not find there.
Thanks,
gillmre

Report •

#19
December 28, 2009 at 12:30:18
Derby:
I am checking it out looks good so far. I was looking for a referance site and this just may be it.
Thanks,
gillmre

Report •


Ask Question