Plugging Addresses Into Formulas

  • Thread starter Thread starter Jim Judkins
  • Start date Start date
J

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.
 
-----Original Message-----
Try using INDIRECT

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

Dan E

"Jim Judkins" <[email protected]> wrote in
Click to expand...
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.
Click to expand...


.
Click to expand...
 
Jim Judkins said:
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.
Click to expand...

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.
 
Back
Top