|
Last row value to another sheet
On one worksheet, I have several rows. Each time a new row of data is added,
I need to take the value from column B in the last row and display it in a D3 on another worksheet. Can anyone help get me started? Ted |
Last row value to another sheet
http://www.excelforum.com/showthread.php?t=519044 go here for an answer -- davesexcel ------------------------------------------------------------------------ davesexcel's Profile: http://www.excelforum.com/member.php...o&userid=31708 View this thread: http://www.excelforum.com/showthread...hreadid=519045 |
Last row value to another sheet
No, I need the value from a column in whatever row is the most recent, the
last row which changes every so often. In other words, a row with new data is entered every week, from this row, the last row with data in it.... Ted "davesexcel" wrote in message ... http://www.excelforum.com/showthread.php?t=519044 go here for an answer -- davesexcel ------------------------------------------------------------------------ davesexcel's Profile: http://www.excelforum.com/member.php...o&userid=31708 View this thread: http://www.excelforum.com/showthread...hreadid=519045 |
Last row value to another sheet
Hi!
Is the data text, numeric or could it be either? Biff "Ted Dawson" wrote in message ... No, I need the value from a column in whatever row is the most recent, the last row which changes every so often. In other words, a row with new data is entered every week, from this row, the last row with data in it.... Ted "davesexcel" wrote in message ... http://www.excelforum.com/showthread.php?t=519044 go here for an answer -- davesexcel ------------------------------------------------------------------------ davesexcel's Profile: http://www.excelforum.com/member.php...o&userid=31708 View this thread: http://www.excelforum.com/showthread...hreadid=519045 |
Last row value to another sheet
The data will always be numeric.
Ted "Biff" wrote in message ... Hi! Is the data text, numeric or could it be either? Biff "Ted Dawson" wrote in message ... No, I need the value from a column in whatever row is the most recent, the last row which changes every so often. In other words, a row with new data is entered every week, from this row, the last row with data in it.... Ted "davesexcel" wrote in message ... http://www.excelforum.com/showthread.php?t=519044 go here for an answer -- davesexcel ------------------------------------------------------------------------ davesexcel's Profile: http://www.excelforum.com/member.php...o&userid=31708 View this thread: http://www.excelforum.com/showthread...hreadid=519045 |
Last row value to another sheet
Try this:
=LOOKUP(9.99999999999999E+307,Sheet1!B:B) Change the sheet name as needed. Biff "Ted Dawson" wrote in message ... The data will always be numeric. Ted "Biff" wrote in message ... Hi! Is the data text, numeric or could it be either? Biff "Ted Dawson" wrote in message ... No, I need the value from a column in whatever row is the most recent, the last row which changes every so often. In other words, a row with new data is entered every week, from this row, the last row with data in it.... Ted "davesexcel" wrote in message ... http://www.excelforum.com/showthread.php?t=519044 go here for an answer -- davesexcel ------------------------------------------------------------------------ davesexcel's Profile: http://www.excelforum.com/member.php...o&userid=31708 View this thread: http://www.excelforum.com/showthread...hreadid=519045 |
Last row value to another sheet
OK, I get it now. Thank you.
Next question: Now I need to add together the last TWO cells in a particular column... can we do that? Ted "Biff" wrote in message ... Try this: =LOOKUP(9.99999999999999E+307,Sheet1!B:B) Change the sheet name as needed. Biff "Ted Dawson" wrote in message ... The data will always be numeric. Ted "Biff" wrote in message ... Hi! Is the data text, numeric or could it be either? Biff "Ted Dawson" wrote in message ... No, I need the value from a column in whatever row is the most recent, the last row which changes every so often. In other words, a row with new data is entered every week, from this row, the last row with data in it.... Ted "davesexcel" wrote in message ... http://www.excelforum.com/showthread.php?t=519044 go here for an answer -- davesexcel ------------------------------------------------------------------------ davesexcel's Profile: http://www.excelforum.com/member.php...o&userid=31708 View this thread: http://www.excelforum.com/showthread...hreadid=519045 |
Last row value to another sheet
add together the last TWO cells in a particular column
Will they always be contiguous? Like this: 25 50 Or might there be blank cells in-between: 25 50 Biff "Ted Dawson" wrote in message ... OK, I get it now. Thank you. Next question: Now I need to add together the last TWO cells in a particular column... can we do that? Ted "Biff" wrote in message ... Try this: =LOOKUP(9.99999999999999E+307,Sheet1!B:B) Change the sheet name as needed. Biff "Ted Dawson" wrote in message ... The data will always be numeric. Ted "Biff" wrote in message ... Hi! Is the data text, numeric or could it be either? Biff "Ted Dawson" wrote in message ... No, I need the value from a column in whatever row is the most recent, the last row which changes every so often. In other words, a row with new data is entered every week, from this row, the last row with data in it.... Ted "davesexcel" wrote in message ... http://www.excelforum.com/showthread.php?t=519044 go here for an answer -- davesexcel ------------------------------------------------------------------------ davesexcel's Profile: http://www.excelforum.com/member.php...o&userid=31708 View this thread: http://www.excelforum.com/showthread...hreadid=519045 |
Last row value to another sheet
They should always be contiguous, but if you care to school all of us in
both scenarios... Ted "Biff" wrote in message ... add together the last TWO cells in a particular column Will they always be contiguous? Like this: 25 50 Or might there be blank cells in-between: 25 50 Biff "Ted Dawson" wrote in message ... OK, I get it now. Thank you. Next question: Now I need to add together the last TWO cells in a particular column... can we do that? Ted "Biff" wrote in message ... Try this: =LOOKUP(9.99999999999999E+307,Sheet1!B:B) Change the sheet name as needed. Biff "Ted Dawson" wrote in message ... The data will always be numeric. Ted "Biff" wrote in message ... Hi! Is the data text, numeric or could it be either? Biff "Ted Dawson" wrote in message ... No, I need the value from a column in whatever row is the most recent, the last row which changes every so often. In other words, a row with new data is entered every week, from this row, the last row with data in it.... Ted "davesexcel" wrote in message ... http://www.excelforum.com/showthread.php?t=519044 go here for an answer -- davesexcel ------------------------------------------------------------------------ davesexcel's Profile: http://www.excelforum.com/member.php...o&userid=31708 View this thread: http://www.excelforum.com/showthread...hreadid=519045 |
Last row value to another sheet
If they're contiguous:
=SUM(OFFSET(B1,MAX(2,COUNT(B:B))-1,,-2)) If there might be blank or empty cells within the range: (it's a little more complicated!): Entered as an array using the key conbo of CTRL,SHIFT,ENTER: =IF(COUNT(B1:B100)<1,0,SUM(B100:INDEX(B1:B100,LARG E(IF(B1:B100<"",ROW(B1:B100)),MIN(2,COUNT(B1:B100 )))))) Biff In this array formula you can't use entire columns as a range reference (in certain expressions). I used a range of B1:B100. You don't need to know the last entry is in B100 but just use a big enough range to cover the last entry. "Ted Dawson" wrote in message ... They should always be contiguous, but if you care to school all of us in both scenarios... Ted "Biff" wrote in message ... add together the last TWO cells in a particular column Will they always be contiguous? Like this: 25 50 Or might there be blank cells in-between: 25 50 Biff "Ted Dawson" wrote in message ... OK, I get it now. Thank you. Next question: Now I need to add together the last TWO cells in a particular column... can we do that? Ted "Biff" wrote in message ... Try this: =LOOKUP(9.99999999999999E+307,Sheet1!B:B) Change the sheet name as needed. Biff "Ted Dawson" wrote in message ... The data will always be numeric. Ted "Biff" wrote in message ... Hi! Is the data text, numeric or could it be either? Biff "Ted Dawson" wrote in message ... No, I need the value from a column in whatever row is the most recent, the last row which changes every so often. In other words, a row with new data is entered every week, from this row, the last row with data in it.... Ted "davesexcel" wrote in message ... http://www.excelforum.com/showthread.php?t=519044 go here for an answer -- davesexcel ------------------------------------------------------------------------ davesexcel's Profile: http://www.excelforum.com/member.php...o&userid=31708 View this thread: http://www.excelforum.com/showthread...hreadid=519045 |
Last row value to another sheet
Biff, you are a Gentleman and a Scholar. Thank you very much.
Ted "Biff" wrote in message ... If they're contiguous: =SUM(OFFSET(B1,MAX(2,COUNT(B:B))-1,,-2)) If there might be blank or empty cells within the range: (it's a little more complicated!): Entered as an array using the key conbo of CTRL,SHIFT,ENTER: =IF(COUNT(B1:B100)<1,0,SUM(B100:INDEX(B1:B100,LARG E(IF(B1:B100<"",ROW(B1:B100)),MIN(2,COUNT(B1:B100 )))))) Biff In this array formula you can't use entire columns as a range reference (in certain expressions). I used a range of B1:B100. You don't need to know the last entry is in B100 but just use a big enough range to cover the last entry. "Ted Dawson" wrote in message ... They should always be contiguous, but if you care to school all of us in both scenarios... Ted "Biff" wrote in message ... add together the last TWO cells in a particular column Will they always be contiguous? Like this: 25 50 Or might there be blank cells in-between: 25 50 Biff "Ted Dawson" wrote in message ... OK, I get it now. Thank you. Next question: Now I need to add together the last TWO cells in a particular column... can we do that? Ted "Biff" wrote in message ... Try this: =LOOKUP(9.99999999999999E+307,Sheet1!B:B) Change the sheet name as needed. Biff "Ted Dawson" wrote in message ... The data will always be numeric. Ted "Biff" wrote in message ... Hi! Is the data text, numeric or could it be either? Biff "Ted Dawson" wrote in message ... No, I need the value from a column in whatever row is the most recent, the last row which changes every so often. In other words, a row with new data is entered every week, from this row, the last row with data in it.... Ted "davesexcel" wrote in message ... http://www.excelforum.com/showthread.php?t=519044 go here for an answer -- davesexcel ------------------------------------------------------------------------ davesexcel's Profile: http://www.excelforum.com/member.php...o&userid=31708 View this thread: http://www.excelforum.com/showthread...hreadid=519045 |
Last row value to another sheet
You're welcome.
Biff "Ted Dawson" wrote in message ... Biff, you are a Gentleman and a Scholar. Thank you very much. Ted "Biff" wrote in message ... If they're contiguous: =SUM(OFFSET(B1,MAX(2,COUNT(B:B))-1,,-2)) If there might be blank or empty cells within the range: (it's a little more complicated!): Entered as an array using the key conbo of CTRL,SHIFT,ENTER: =IF(COUNT(B1:B100)<1,0,SUM(B100:INDEX(B1:B100,LARG E(IF(B1:B100<"",ROW(B1:B100)),MIN(2,COUNT(B1:B100 )))))) Biff In this array formula you can't use entire columns as a range reference (in certain expressions). I used a range of B1:B100. You don't need to know the last entry is in B100 but just use a big enough range to cover the last entry. "Ted Dawson" wrote in message ... They should always be contiguous, but if you care to school all of us in both scenarios... Ted "Biff" wrote in message ... add together the last TWO cells in a particular column Will they always be contiguous? Like this: 25 50 Or might there be blank cells in-between: 25 50 Biff "Ted Dawson" wrote in message ... OK, I get it now. Thank you. Next question: Now I need to add together the last TWO cells in a particular column... can we do that? Ted "Biff" wrote in message ... Try this: =LOOKUP(9.99999999999999E+307,Sheet1!B:B) Change the sheet name as needed. Biff "Ted Dawson" wrote in message ... The data will always be numeric. Ted "Biff" wrote in message ... Hi! Is the data text, numeric or could it be either? Biff "Ted Dawson" wrote in message ... No, I need the value from a column in whatever row is the most recent, the last row which changes every so often. In other words, a row with new data is entered every week, from this row, the last row with data in it.... Ted "davesexcel" wrote in message ... http://www.excelforum.com/showthread.php?t=519044 go here for an answer -- davesexcel ------------------------------------------------------------------------ davesexcel's Profile: http://www.excelforum.com/member.php...o&userid=31708 View this thread: http://www.excelforum.com/showthread...hreadid=519045 |
Last row value to another sheet
Define BigNum as referring to 9.99999999999999E+307 then invoke:
=LOOKUP(BigNum,B2:B65536)+ LOOKUP(BigNum,B2:INDEX(B2:B65536, MATCH(BigNum,B2:B65536)-1)) Biff wrote: If they're contiguous: =SUM(OFFSET(B1,MAX(2,COUNT(B:B))-1,,-2)) If there might be blank or empty cells within the range: (it's a little more complicated!): Entered as an array using the key conbo of CTRL,SHIFT,ENTER: =IF(COUNT(B1:B100)<1,0,SUM(B100:INDEX(B1:B100,LARG E(IF(B1:B100<"",ROW(B1:B100)),MIN(2,COUNT(B1:B100 )))))) Biff In this array formula you can't use entire columns as a range reference (in certain expressions). I used a range of B1:B100. You don't need to know the last entry is in B100 but just use a big enough range to cover the last entry. "Ted Dawson" wrote in message ... They should always be contiguous, but if you care to school all of us in both scenarios... Ted "Biff" wrote in message ... add together the last TWO cells in a particular column Will they always be contiguous? Like this: 25 50 Or might there be blank cells in-between: 25 50 Biff "Ted Dawson" wrote in message ... OK, I get it now. Thank you. Next question: Now I need to add together the last TWO cells in a particular column... can we do that? Ted "Biff" wrote in message .. . Try this: =LOOKUP(9.99999999999999E+307,Sheet1!B:B) Change the sheet name as needed. Biff "Ted Dawson" wrote in message ... The data will always be numeric. Ted "Biff" wrote in message ... Hi! Is the data text, numeric or could it be either? Biff "Ted Dawson" wrote in message l... No, I need the value from a column in whatever row is the most recent, the last row which changes every so often. In other words, a row with new data is entered every week, from this row, the last row with data in it.... Ted "davesexcel" <davesexcel.2462gc_1141511704.1265@excelfo rum-nospam.com wrote in message news:davesexcel.2462gc_1141511704.1265@exc elforum-nospam.com... http://www.excelforum.com/showthread.php?t=519044 go here for an answer -- davesexcel ------------------------------------------------------------------------ davesexcel's Profile: http://www.excelforum.com/member.php...o&userid=31708 View this thread: http://www.excelforum.com/showthread...hreadid=519045 |
| All times are GMT +1. The time now is 11:11 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com