Plugging Addresses Into Formulas

[Jim Judkins]

I have a table (27 columns, 27 rows), and I want to be
able to pick out values based on columns & rows. For
example, the value for S7 is 5. I used ADDRESS to get the
correct column & row (based on column/row headings), but
have been unsuccessful in using that address to extract
the cell value. For example, INDEX will give me the value
if I plug in =INDEX(array,7:7,S:S). But if I use =INDEX
(array,[cell location that has row number],[cell location
that has column letter]), it doesn't give the value in
S7. My guess is that the formula doesn't see the data in
the cells that list the column and row addresses as cell
addresses.

 

[Jim J.]

-----Original Message-----
Try using INDIRECT

ie ( = INDIRECT("S7") ) should return 5

Dan E

"Jim Judkins" <[email protected]> wrote in

message news:[email protected]...

I have a table (27 columns, 27 rows), and I want to be
able to pick out values based on columns & rows. For
example, the value for S7 is 5. I used ADDRESS to get the
correct column & row (based on column/row headings), but
have been unsuccessful in using that address to extract
the cell value. For example, INDEX will give me the value
if I plug in =INDEX(array,7:7,S:S). But if I use =INDEX
(array,[cell location that has row number],[cell location
that has column letter]), it doesn't give the value in
S7. My guess is that the formula doesn't see the data in
the cells that list the column and row addresses as cell
addresses.

.

 

[Anon]

I have a table (27 columns, 27 rows), and I want to be
able to pick out values based on columns & rows. For
example, the value for S7 is 5. I used ADDRESS to get the
correct column & row (based on column/row headings), but
have been unsuccessful in using that address to extract
the cell value. For example, INDEX will give me the value
if I plug in =INDEX(array,7:7,S:S). But if I use =INDEX
(array,[cell location that has row number],[cell location
that has column letter]), it doesn't give the value in
S7. My guess is that the formula doesn't see the data in
the cells that list the column and row addresses as cell
addresses.

I suggest OFFSET as the function you should investigate. For example,
=OFFSET($A$1,3,4,1,1)
will return the value in E4. The 3 and 4 can be calculated values.
Similarly,
=OFFSET(array,3,4,1,1)
starts at the top left of array.