| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Linking 2 spreadsheet using data from the last row of spreadsheet
I'm using MS Excel 2003. I have 2 spreadsheet, spreadsheet 1 is updated daily
with new rows added daily. Spreadsheet 2 needs to extract data from the last row of data from spreadsheet 1. How do I link spreadsheet 2 to spreadsheet 1 so that it will always get the data from the last row of spreadsheet 1 automatically? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Linking 2 spreadsheet using data from the last row of spreadsheet
Assume the "last row of data" is determined in Sheet1's col A
In Sheet2, you could use something like this in say, A2: =LOOKUP(2,1/(Sheet1!$A$2:$A$100<""),Sheet1!A2:A100) to extract the last row value in Sheet1's col A. Copy A2 across to return the other cols' values (ie col B, col C, etc) for that last row in Sheet1's col A -- Max Singapore http://savefile.com/projects/236895 Downloads:23,000 Files:370 Subscribers:66 xdemechanik --- "Mike" wrote: I'm using MS Excel 2003. I have 2 spreadsheet, spreadsheet 1 is updated daily with new rows added daily. Spreadsheet 2 needs to extract data from the last row of data from spreadsheet 1. How do I link spreadsheet 2 to spreadsheet 1 so that it will always get the data from the last row of spreadsheet 1 automatically? |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Linking 2 spreadsheet using data from the last row of spreadsh
Hi Max,
Thanks for your syntax. It work, as it returned the data from the last row from spreadsheet 1. However, as I add a new entry row in sheet 1, sheet 2 cannot get the changes automatically. I have to repeat the whole process again and each time I do that, it keep asking me to specify the source data. Regards, Mike "Max" wrote: Assume the "last row of data" is determined in Sheet1's col A In Sheet2, you could use something like this in say, A2: =LOOKUP(2,1/(Sheet1!$A$2:$A$100<""),Sheet1!A2:A100) to extract the last row value in Sheet1's col A. Copy A2 across to return the other cols' values (ie col B, col C, etc) for that last row in Sheet1's col A -- Max Singapore http://savefile.com/projects/236895 Downloads:23,000 Files:370 Subscribers:66 xdemechanik --- "Mike" wrote: I'm using MS Excel 2003. I have 2 spreadsheet, spreadsheet 1 is updated daily with new rows added daily. Spreadsheet 2 needs to extract data from the last row of data from spreadsheet 1. How do I link spreadsheet 2 to spreadsheet 1 so that it will always get the data from the last row of spreadsheet 1 automatically? |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Linking 2 spreadsheet using data from the last row of spreadsh
Max's formula works for me!
Are both your 'spreadsheets' in the same workbook? Have you set Calculation Option to automatic? Try the formula in a new workbook - does it return the correct result? "Mike" wrote: Hi Max, Thanks for your syntax. It work, as it returned the data from the last row from spreadsheet 1. However, as I add a new entry row in sheet 1, sheet 2 cannot get the changes automatically. I have to repeat the whole process again and each time I do that, it keep asking me to specify the source data. Regards, Mike "Max" wrote: Assume the "last row of data" is determined in Sheet1's col A In Sheet2, you could use something like this in say, A2: =LOOKUP(2,1/(Sheet1!$A$2:$A$100<""),Sheet1!A2:A100) to extract the last row value in Sheet1's col A. Copy A2 across to return the other cols' values (ie col B, col C, etc) for that last row in Sheet1's col A -- Max Singapore http://savefile.com/projects/236895 Downloads:23,000 Files:370 Subscribers:66 xdemechanik --- "Mike" wrote: I'm using MS Excel 2003. I have 2 spreadsheet, spreadsheet 1 is updated daily with new rows added daily. Spreadsheet 2 needs to extract data from the last row of data from spreadsheet 1. How do I link spreadsheet 2 to spreadsheet 1 so that it will always get the data from the last row of spreadsheet 1 automatically? |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Linking 2 spreadsheet using data from the last row of spreadsh
.. However, as I add a new entry row in sheet 1,
sheet 2 cannot get the changes automatically It should work ok, as long as new data entry always gets incrementally added in the key Sheet1's col A (as per the header assumption in the earlier response). If you omit concurrent data entry in the key col A and only update other cols, then of course it doesn't work. You need to establish a key col which always gets updated irrespective, for new row entries. If it isn't col A but its say, col C instead, adapt the expression accordingly, ie use this in Sheet2's A2: =LOOKUP(2,1/(Sheet1!$C$2:$C$100<""),Sheet1!A2:A100) and copy it across. Try it again, it should work ok for you, as it did for me, and for Ron, too (thanks for the support, Ron!). -- Max Singapore http://savefile.com/projects/236895 Downloads:23,000 Files:370 Subscribers:66 xdemechanik --- "Mike" wrote: Hi Max, Thanks for your syntax. It work, as it returned the data from the last row from spreadsheet 1. However, as I add a new entry row in sheet 1, sheet 2 cannot get the changes automatically. I have to repeat the whole process again and each time I do that, it keep asking me to specify the source data. Regards, Mike |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Linking 2 spreadsheet using data from the last row of spreadsh
Hi I have a similar task to Mike's and using MS Excel 2003. I have 2
spreadsheet, spreadsheet 1 is updated daily with NEW DATA NEXT TO THE EXISTING rows OF INFO. Spreadsheet 2 needs to READ/extract data from ONLY THE ADDITIONAL DATA from spreadsheet 1. How do I link spreadsheet 2 to spreadsheet 1 so that it will always get the data from spreadsheet 1 automatically? VENNESSE "Max" wrote: .. However, as I add a new entry row in sheet 1, sheet 2 cannot get the changes automatically It should work ok, as long as new data entry always gets incrementally added in the key Sheet1's col A (as per the header assumption in the earlier response). If you omit concurrent data entry in the key col A and only update other cols, then of course it doesn't work. You need to establish a key col which always gets updated irrespective, for new row entries. If it isn't col A but its say, col C instead, adapt the expression accordingly, ie use this in Sheet2's A2: =LOOKUP(2,1/(Sheet1!$C$2:$C$100<""),Sheet1!A2:A100) and copy it across. Try it again, it should work ok for you, as it did for me, and for Ron, too (thanks for the support, Ron!). -- Max Singapore http://savefile.com/projects/236895 Downloads:23,000 Files:370 Subscribers:66 xdemechanik --- "Mike" wrote: Hi Max, Thanks for your syntax. It work, as it returned the data from the last row from spreadsheet 1. However, as I add a new entry row in sheet 1, sheet 2 cannot get the changes automatically. I have to repeat the whole process again and each time I do that, it keep asking me to specify the source data. Regards, Mike |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Linking 2 spreadsheet using data from the last row of spreadsh
I'm not sure that your requirements are similar to Mike's. Mike wanted only
the last row's data to be reflected over. You seem to want more than that, besides the orientation differences. If you meant to extract only the last col's data over from Sheet1, you could try something like this: Data assumed in Sheet1, row2 down, where the last col of data in each row* (ie the rightmost cell in each row) is desired to be auto-reflected in Sheet2 in say, col B. *data may vary from row to row, eg in row2 it may be E2, in row3, its K3, and so on In Sheet2, Put in B2: =LOOKUP(2,1/(Sheet1!2:2<""),Sheet1!2:2) Copy down as far as required If the above isn't it, suggest you start your own thread. Illustrate clearly with sample data what you have in Sheet1, and the expected results in Sheet2. Do high-five this response by clicking on the YES button below if it helped in any way -- Max Singapore http://savefile.com/projects/236895 Downloads:23,000 Files:370 Subscribers:66 xdemechanik --- "vennesse" wrote: Hi I have a similar task to Mike's and using MS Excel 2003. I have 2 spreadsheet, spreadsheet 1 is updated daily with NEW DATA NEXT TO THE EXISTING rows OF INFO. Spreadsheet 2 needs to READ/extract data from ONLY THE ADDITIONAL DATA from spreadsheet 1. How do I link spreadsheet 2 to spreadsheet 1 so that it will always get the data from spreadsheet 1 automatically? VENNESSE |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Linking 2 spreadsheet using data from the last row of spreadsh
Hi I have a similar task to Mike's and using MS Excel 2003. I have 2
spreadsheet, spreadsheet 1 is updated daily with NEW DATA NEXT TO THE EXISTING rows OF INFO. Spreadsheet 2 needs to READ/extract data from ONLY THE ADDITIONAL DATA from spreadsheet 1. How do I link spreadsheet 2 to spreadsheet 1 so that it will always get the data from spreadsheet 1 automatically? VENNESSE "Mike" wrote: Hi Max, Thanks for your syntax. It work, as it returned the data from the last row from spreadsheet 1. However, as I add a new entry row in sheet 1, sheet 2 cannot get the changes automatically. I have to repeat the whole process again and each time I do that, it keep asking me to specify the source data. Regards, Mike "Max" wrote: Assume the "last row of data" is determined in Sheet1's col A In Sheet2, you could use something like this in say, A2: =LOOKUP(2,1/(Sheet1!$A$2:$A$100<""),Sheet1!A2:A100) to extract the last row value in Sheet1's col A. Copy A2 across to return the other cols' values (ie col B, col C, etc) for that last row in Sheet1's col A -- Max Singapore http://savefile.com/projects/236895 Downloads:23,000 Files:370 Subscribers:66 xdemechanik --- "Mike" wrote: I'm using MS Excel 2003. I have 2 spreadsheet, spreadsheet 1 is updated daily with new rows added daily. Spreadsheet 2 needs to extract data from the last row of data from spreadsheet 1. How do I link spreadsheet 2 to spreadsheet 1 so that it will always get the data from the last row of spreadsheet 1 automatically? |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Linking 2 spreadsheet using data from the last row of spreadsh
Hi I have a similar task to Mike's and using MS Excel 2003. I have 2
spreadsheet, spreadsheet 1 is updated daily with NEW DATA NEXT TO THE EXISTING rows OF INFO. Spreadsheet 2 needs to READ/extract data from ONLY THE ADDITIONAL DATA from spreadsheet 1. How do I link spreadsheet 2 to spreadsheet 1 so that it will always get the data from spreadsheet 1 automatically? VENNESSE "Mike" wrote: Hi Max, Thanks for your syntax. It work, as it returned the data from the last row from spreadsheet 1. However, as I add a new entry row in sheet 1, sheet 2 cannot get the changes automatically. I have to repeat the whole process again and each time I do that, it keep asking me to specify the source data. Regards, Mike "Max" wrote: Assume the "last row of data" is determined in Sheet1's col A In Sheet2, you could use something like this in say, A2: =LOOKUP(2,1/(Sheet1!$A$2:$A$100<""),Sheet1!A2:A100) to extract the last row value in Sheet1's col A. Copy A2 across to return the other cols' values (ie col B, col C, etc) for that last row in Sheet1's col A -- Max Singapore http://savefile.com/projects/236895 Downloads:23,000 Files:370 Subscribers:66 xdemechanik --- "Mike" wrote: I'm using MS Excel 2003. I have 2 spreadsheet, spreadsheet 1 is updated daily with new rows added daily. Spreadsheet 2 needs to extract data from the last row of data from spreadsheet 1. How do I link spreadsheet 2 to spreadsheet 1 so that it will always get the data from the last row of spreadsheet 1 automatically? |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Linking 2 spreadsheet using data from the last row of spreadsh
I have a similar task and am using MS Excel 2003. I have 2 spreadsheet,
spreadsheet 1 is updated daily DEPENDING ON THE DATA (ROWS). Spreadsheet 2 needs to extract data from the last UPDATED ROW OF DATA FRM SPREADSHEET 1. How do I link spreadsheet 2 to spreadsheet 1 so that it will always get the data FROM ANY ROWS THT HAVE BEEN UPDATED of spreadsheet 1 automatically? VENNESSE "Max" wrote: Assume the "last row of data" is determined in Sheet1's col A In Sheet2, you could use something like this in say, A2: =LOOKUP(2,1/(Sheet1!$A$2:$A$100<""),Sheet1!A2:A100) to extract the last row value in Sheet1's col A. Copy A2 across to return the other cols' values (ie col B, col C, etc) for that last row in Sheet1's col A -- Max Singapore http://savefile.com/projects/236895 Downloads:23,000 Files:370 Subscribers:66 xdemechanik --- "Mike" wrote: I'm using MS Excel 2003. I have 2 spreadsheet, spreadsheet 1 is updated daily with new rows added daily. Spreadsheet 2 needs to extract data from the last row of data from spreadsheet 1. How do I link spreadsheet 2 to spreadsheet 1 so that it will always get the data from the last row of spreadsheet 1 automatically? |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Linking 2 spreadsheet using data from the last row of spreadsh
"Max" wrote: I also have a similar task and using MS Excel 2003. I have 2 spreadsheet, spreadsheet 1 is updated daily with new DATA ADDED NEXT TO THE ROWS OF DATA daily. Spreadsheet 2 needs to extract THE UPDATED data from the rows of data from spreadsheet 1. How do I link spreadsheet 2 to spreadsheet 1 so that it will always get the ROW OF data from the row of spreadsheet 1 automatically? The new data are usually not updated in order. EX: Column B to E are populated with information and DATA updated in F through M. Assume the "last row of data" is determined in Sheet1's col A In Sheet2, you could use something like this in say, A2: =LOOKUP(2,1/(Sheet1!$A$2:$A$100<""),Sheet1!A2:A100) to extract the last row value in Sheet1's col A. Copy A2 across to return the other cols' values (ie col B, col C, etc) for that last row in Sheet1's col A -- Max Singapore http://savefile.com/projects/236895 Downloads:23,000 Files:370 Subscribers:66 xdemechanik --- "Mike" wrote: I'm using MS Excel 2003. I have 2 spreadsheet, spreadsheet 1 is updated daily with new rows added daily. Spreadsheet 2 needs to extract data from the last row of data from spreadsheet 1. How do I link spreadsheet 2 to spreadsheet 1 so that it will always get the data from the last row of spreadsheet 1 automatically? |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Linking 2 spreadsheet using data from the last row of spreadsh
Hi Venesse
Are you saying that columns B:E are constant on Sheet1, with new data being entered in columns F:M which overwrites what was there before? If so, and assuming your Sheet 2 has values in column A that match column A of Sheet 1, then it is enter in B2 of Sheet2 =VLOOKUP($A2,Sheet1!$A$:$M$,Column(F2),0) Copy across and down as far as required -- Regards Roger Govier "vennesse" wrote in message ... "Max" wrote: I also have a similar task and using MS Excel 2003. I have 2 spreadsheet, spreadsheet 1 is updated daily with new DATA ADDED NEXT TO THE ROWS OF DATA daily. Spreadsheet 2 needs to extract THE UPDATED data from the rows of data from spreadsheet 1. How do I link spreadsheet 2 to spreadsheet 1 so that it will always get the ROW OF data from the row of spreadsheet 1 automatically? The new data are usually not updated in order. EX: Column B to E are populated with information and DATA updated in F through M. Assume the "last row of data" is determined in Sheet1's col A In Sheet2, you could use something like this in say, A2: =LOOKUP(2,1/(Sheet1!$A$2:$A$100<""),Sheet1!A2:A100) to extract the last row value in Sheet1's col A. Copy A2 across to return the other cols' values (ie col B, col C, etc) for that last row in Sheet1's col A -- Max Singapore http://savefile.com/projects/236895 Downloads:23,000 Files:370 Subscribers:66 xdemechanik --- "Mike" wrote: I'm using MS Excel 2003. I have 2 spreadsheet, spreadsheet 1 is updated daily with new rows added daily. Spreadsheet 2 needs to extract data from the last row of data from spreadsheet 1. How do I link spreadsheet 2 to spreadsheet 1 so that it will always get the data from the last row of spreadsheet 1 automatically? |
|
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Linking 2 spreadsheet using data from the last row of spreadsh
Hi I have a similar task to Mike's and using MS Excel 2003. I have 2
spreadsheet, spreadsheet 1 is updated daily with NEW DATA NEXT TO THE EXISTING rows OF INFO. Spreadsheet 2 needs to READ/extract data from ONLY THE ADDITIONAL DATA from spreadsheet 1. How do I link spreadsheet 2 to spreadsheet 1 so that it will always get the data from spreadsheet 1 automatically? VENNESSE "Max" wrote: Assume the "last row of data" is determined in Sheet1's col A In Sheet2, you could use something like this in say, A2: =LOOKUP(2,1/(Sheet1!$A$2:$A$100<""),Sheet1!A2:A100) to extract the last row value in Sheet1's col A. Copy A2 across to return the other cols' values (ie col B, col C, etc) for that last row in Sheet1's col A -- Max Singapore http://savefile.com/projects/236895 Downloads:23,000 Files:370 Subscribers:66 xdemechanik --- "Mike" wrote: I'm using MS Excel 2003. I have 2 spreadsheet, spreadsheet 1 is updated daily with new rows added daily. Spreadsheet 2 needs to extract data from the last row of data from spreadsheet 1. How do I link spreadsheet 2 to spreadsheet 1 so that it will always get the data from the last row of spreadsheet 1 automatically? |
|
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Linking 2 spreadsheet using data from the last row of spreadsh
Hi I have a similar task to Mike's and using MS Excel 2003. I have 2
spreadsheet, spreadsheet 1 is updated daily with NEW DATA NEXT TO THE EXISTING rows OF INFO. Spreadsheet 2 needs to READ/extract data from ONLY THE ADDITIONAL DATA from spreadsheet 1. How do I link spreadsheet 2 to spreadsheet 1 so that it will always get the data from spreadsheet 1 automatically? VENNESSE "Max" wrote: Assume the "last row of data" is determined in Sheet1's col A In Sheet2, you could use something like this in say, A2: =LOOKUP(2,1/(Sheet1!$A$2:$A$100<""),Sheet1!A2:A100) to extract the last row value in Sheet1's col A. Copy A2 across to return the other cols' values (ie col B, col C, etc) for that last row in Sheet1's col A -- Max Singapore http://savefile.com/projects/236895 Downloads:23,000 Files:370 Subscribers:66 xdemechanik --- "Mike" wrote: I'm using MS Excel 2003. I have 2 spreadsheet, spreadsheet 1 is updated daily with new rows added daily. Spreadsheet 2 needs to extract data from the last row of data from spreadsheet 1. How do I link spreadsheet 2 to spreadsheet 1 so that it will always get the data from the last row of spreadsheet 1 automatically? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Linking spreadsheet | Excel Worksheet Functions | |||
| linking data from sharepoint to excel spreadsheet? | Excel Worksheet Functions | |||
| Linking two spreadsheet, pulling data from one cell to another, data is being truncated | Excel Worksheet Functions | |||
| How do I copy spreadsheet data as shown to another spreadsheet? | Excel Discussion (Misc queries) | |||
| Linking Data in Access to a Spreadsheet | Links and Linking in Excel | |||
Hybrid Mode
