A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Discussion (Misc queries)
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Linking 2 spreadsheet using data from the last row of spreadsheet



 
 
Thread Tools Display Modes
  #1  
Old February 5th 09, 02:40 AM posted to microsoft.public.excel.misc
Mike
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?
Ads
  #2  
Old February 5th 09, 04:37 AM posted to microsoft.public.excel.misc
Max
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  
Old February 5th 09, 06:26 AM posted to microsoft.public.excel.misc
Mike
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  
Old February 5th 09, 07:17 AM 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  
Old February 5th 09, 08:18 AM posted to microsoft.public.excel.misc
Max
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  
Old February 11th 09, 08:17 AM posted to microsoft.public.excel.misc
vennesse
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  
Old February 11th 09, 09:29 AM posted to microsoft.public.excel.misc
vennesse
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  
Old February 11th 09, 10:02 AM posted to microsoft.public.excel.misc
vennesse
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  
Old February 11th 09, 10:03 AM posted to microsoft.public.excel.misc
vennesse
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  
Old February 11th 09, 10:03 AM posted to microsoft.public.excel.misc
vennesse
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?

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

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 12:31 AM.


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