Row??

  • Thread starter Thread starter j.ruderman
  • Start date Start date
J

j.ruderman

I have a Row# listed in a cell and a known column. How can I combine the two?
A1 contains the row# with B being the known column.
Result of the formula woulld be 10
a b c
1 4
2
3
4 10
5

Hope this make sense.
Thanks
 
Try

=INDIRECT(ADDRESS(A1,COLUMN(B1)))

A1 is the cell with the row #. COLUMN(B1) returns the number of column B.
ADDRESS converts those into the address $B$4, and INDIRECT returns the value
in that cell.

Hope this helps,

Hutch
 
Thank you for the quick reply. This works perfectly, however, I have a follow
up question.
a b c
1 4 1 7
2 2 8
3 3 9
4 4 10
5

Using your formula (I changed B1 to C1) is there a way to add a "Min"
function for B1:B4. Understand that the row# (A1) will always be different
therefore needing to somehow incorp it into your original formula. I would
like to explain what I'm actually tring to do but I would have to write a
book.

Thanks again
 
Another one:

=index(b:b,a1)

To return the value from the row number in A1 in column B.
 
I don't understand what you want. How should MIN(B1:B4) come into play in the
formula? Is the row stil given in A1?

Hutch
 
Yes and No. I tried to simplify it a little and I'm most likley taking the
wrong approch all together. My sheet is a daily work log. The log will
contain start and stop times with randomly located blank rows, up to 5,
seperating work shifts for that day.
eg.
a b
1 6:00 a 12:00 p
2 12:15 p 2:00 p
3
4 5:00 p 8:00 p
5
6 9:00 p 9:30 p
7 9:45 p 11:00 p

Result of formula's would, in this case, be in cells B2-B4. C2-C4 are
already found.
A B C
1 Start Stop
2 Shift 1 6:00 a 2:00 p
3 Shift 2 5:00 p 8:00 p
4 Shift 3 9:00 p 11:00 p

My first approch was to find the blank rows -1 and use the
"indirect(address)" to find the stop time of the shift. Now I need to find
the start time for that same shift. I was tring to use the Min function, in
this case column A1 thru A- row# located in a hidding column, say AA1, but I
can't seem to figure out how to incorporate the functions.
Sorry for the book but I hope this make sense.
Thank you
 
Disregard, I figured it out. It simply used the found row# +2 to land me on
the start time of the next shift. The 1st shift is fixed. Thanks for the
original formula to which got me started.
 
Back
Top