Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old February 5th 09, 03:40 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 3,101
Default 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   Report Post  
Old February 5th 09, 05:37 AM posted to microsoft.public.excel.misc
Max Max is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 9,221
Default 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   Report Post  
Old February 5th 09, 07:26 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 3,101
Default 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   Report Post  
Old February 5th 09, 08:17 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2007
Posts: 345
Default 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   Report Post  
Old February 5th 09, 09:18 AM posted to microsoft.public.excel.misc
Max Max is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 9,221
Default 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   Report Post  
Old February 11th 09, 09:17 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Feb 2009
Posts: 10
Default 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?

  #7   Report Post  
Old February 11th 09, 10:29 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Feb 2009
Posts: 10
Default 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?

  #8   Report Post  
Old February 11th 09, 11:02 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Feb 2009
Posts: 10
Default 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?

  #9   Report Post  
Old February 11th 09, 11:03 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Feb 2009
Posts: 10
Default 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


  #10   Report Post  
Old February 11th 09, 11:03 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Feb 2009
Posts: 10
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Linking spreadsheet mrc1986 via OfficeKB.com Excel Worksheet Functions 0 October 6th 08 08:16 PM
linking data from sharepoint to excel spreadsheet? phrog164 Excel Worksheet Functions 0 June 12th 08 09:03 PM
Linking two spreadsheet, pulling data from one cell to another, data is being truncated Ben Excel Worksheet Functions 0 September 13th 07 11:41 PM
How do I copy spreadsheet data as shown to another spreadsheet? trainer07 Excel Discussion (Misc queries) 2 August 7th 06 09:39 PM
Linking Data in Access to a Spreadsheet carrol Links and Linking in Excel 4 July 21st 05 11:12 PM


All times are GMT +1. The time now is 02:58 AM.

Powered by vBulletin® Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
Copyright 2004-2017 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017