Russ
Member
Registered: 14th Mar 04
Location: Armchair
User status: Offline
|
Hi,
Basically, i have a column in workbook1 that is updated with a name of the last person to book something out, when the next person books it, there name would go in cell below, and so on, so i always know who had it and when.
i want to link the last person to book it out to another workbook. i know how to link a cell to a cell ='c:\path\[file name.xls]Sheet1'!$J$2) , but when the cell in workbook 1 changes to the next cell down i don't know the formula (or even if its possible) to update workbook2
[Edited on 05-07-2012 by Russ]
|
Whittie
Member
Registered: 11th Aug 06
Location: North Wales Drives: BMW, Corsa & Fiat
User status: Offline
|
I didn't know that's possible...
|
Russ
Member
Registered: 14th Mar 04
Location: Armchair
User status: Offline
|
it might not be, but would save me a whole click of the mouse.
|
Brett
Premium Member
Registered: 16th Dec 02
Location: Manchester
User status: Offline
|
It will be possible
|
Gaz
Member
Registered: 24th Aug 03
Location: Widnes, Cheshire
User status: Offline
|
it is possible, but as long as the files are on the same PC or she network shared drive...
the easiest way to try and explain this is just to have both files open, simply click into your cell you want the info to display and then use the "dummys guide" (not sure what else to call it) to then select the other file and cell you want... is will do something similar to what you have above but without the { and }
this also might help:
http://office.microsoft.com/en-us/excel-help/create-a-link-to-another-cell-workbook-or-program-HP005199514.aspx
[Edited on 05-07-2012 by Gaz]
|
pow
Premium Member
Registered: 11th Sep 06
Location: Hazlemere, Buckinghamshire
User status: Offline
|
It is perfectly possible, just have to be able to have write access to it at all times
|
Russ
Member
Registered: 14th Mar 04
Location: Armchair
User status: Offline
|
quote:
Originally posted by pow
It is perfectly possible, just have to be able to have write access to it at all times
have that.
i can link one cell to another, just can't get it to automatically update to the next cell down the column on the source sheet when it is filled in without changing the formula.
|
Matt L
Member
Registered: 17th Apr 06
User status: Offline
|
=INDEX('['c:\path\[file name.xls]Sheet1'!$J$J,COUNTA('['c:\path\[file name.xls]Sheet1'!$J$J),1)
that should work,
basically, type =index( then select the column you want to look the infor up from), Counta (select same column again) , 1 )
this will return the last entry in the colum you specify
[Edited on 05-07-2012 by Matt L]
|
A2H GO
Member
Registered: 14th Sep 04
Location: Stoke
User status: Offline
|
Or you could do it using an on open event macro in workbook 2 and some basic VBA.
|
Russ
Member
Registered: 14th Mar 04
Location: Armchair
User status: Offline
|
Ash, we've established I can't do basic level excel without involving VB
|
