Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Row??
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Row??
try
=INDIRECT("b"&a1) -- Don Guillett Microsoft MVP Excel SalesAid Software "j.ruderman" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Row??
Try this:
=INDIRECT("B"&A1) Hope this helps. Pete "j.ruderman" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Row??
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 "j.ruderman" wrote: 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Row??
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 "Tom Hutchins" wrote: 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 "j.ruderman" wrote: 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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Row??
Another one:
=index(b:b,a1) To return the value from the row number in A1 in column B. j.ruderman wrote: 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 -- Dave Peterson |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Row??
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 "j.ruderman" wrote: 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 "Tom Hutchins" wrote: 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 "j.ruderman" wrote: 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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Row??
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 "Tom Hutchins" wrote: 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 "j.ruderman" wrote: 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 "Tom Hutchins" wrote: 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 "j.ruderman" wrote: 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 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Row??
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. "j.ruderman" wrote: 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 "Tom Hutchins" wrote: 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 "j.ruderman" wrote: 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 "Tom Hutchins" wrote: 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 "j.ruderman" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|