Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
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  
Posted to microsoft.public.excel.misc
Max Max is offline
external usenet poster
 
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  
Posted to microsoft.public.excel.misc
external usenet poster
 
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  
Posted to microsoft.public.excel.misc
external usenet poster
 
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  
Posted to microsoft.public.excel.misc
Max Max is offline
external usenet poster
 
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  
Posted to microsoft.public.excel.misc
external usenet poster
 
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  
Posted to microsoft.public.excel.misc
external usenet poster
 
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  
Posted to microsoft.public.excel.misc
external usenet poster
 
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  
Posted to microsoft.public.excel.misc
external usenet poster
 
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  
Posted to microsoft.public.excel.misc
external usenet poster
 
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?



  #11   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
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

"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?

  #12   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
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

"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?

  #13   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,480
Default 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?


  #14   Report Post  
Posted to microsoft.public.excel.misc
Max Max is offline
external usenet poster
 
Posts: 9,221
Default 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


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 05:16 PM.

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

About Us

"It's about Microsoft Excel"