Linking 2 spreadsheet using data from the last row of spreadsheet

[Mike]

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?

 

[Max]

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]

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

 

[Ron@Buy]

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?

 

[Max]

.. 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
---

 

[vennesse]

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

 

[vennesse]

:
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.

 

[vennesse]

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

 

[vennesse]

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

 

[vennesse]

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

 

[vennesse]

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

 

[vennesse]

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

 

[Roger Govier]

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

 

[Max]

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