Need Help! Basic user of Excel, require professional knowledge!

[MomoMorrice]

Hello all that read this thread. I'm very new to posting this sort of problem, or indeed any problem over the internet. So apologies if I dont quite get across what I am looking for. I'm also have only basic knowledge of Excel, so please be patient if I don't seem to quite understand.

Problem:

• 
  [*=2]I have two workbooks, workbook A and workbook B.
  [*=2]Workbook A has a worksheet within it that gets regularly updated.
  [*=2]Within this worksheet a couple of columns exist (Date column, Name of Staff Member and Initials of Staff Member) contain one cell filled in ( for example Date: 12/03/13, Staff Member Joe Bloggs, Initials JB) but the rest of the cells in each column is left blank to be filled in while working.
  [*=2]Workbook B has a different worksheet where the columns are in a different area of the spreadhseet. But should be filled in with exactly the same data from Workbook A.
  [*=2]But I have come to the conclusion there should be a way of the data from workbook A automatically inputting into the same columns in Workbook B without the need for manual input.
  [*=2]I understand you can Paste link the data, however I would like for when I input the data into Workbook A it to automatically update Workbook B without me needing to have both workbooks open at the same time (as Paste Link works under this basis of having both open) i.e. Workbook A being open and Workbook B being closed.

I hope there is a solution to my problem out there, there's bound to be!
Thank you for reading and hope you can find the solution I am looking for!

 

[pjmorris]

my thought would be:

If the Staff Member is in cell B3 in Sheet 1 of Workbook A, then wherever you need that information in Workbook B enter: =[WorkbookA.xlsx]Sheet1!$B3 and then copy it down to wherever you need it. This will automatically update whenever both are open and will update when you open Workbook B.

Hope this helps.

 

[Chris Mack]

I think the easiest way to do this is as follows:

- Open both workbooks.
- In Workbook B, select the cell where the data from cell A1 in Workbook A should be.
- Type "=", then click on cell A1 in Workbook A.
- Enter this as your formula, and copy this across/down, until you have what you were looking for.

Does this work?

Chris

 

[MomoMorrice]

Hey Peter,

Nah I'm afraid I encounter a problem that when I go to update Workbook B after closing it and editing cells in Workbook A.

It sasy that there is an error and the links cannot function and therefore it cannot update it.

Any solution to this problem? I appreciate your help!

 

[MomoMorrice]

I think the easiest way to do this is as follows:

- Open both workbooks.
- In Workbook B, select the cell where the data from cell A1 in Workbook A should be.
- Type "=", then click on cell A1 in Workbook A.
- Enter this as your formula, and copy this across/down, until you have what you were looking for.

Does this work?

Chris

Yeah this seems to work. Except it doesn't want to copy down in descending order, just continually stuck with C9 or C10 if I use those cells at the beginning.. Any ideas??

 

[MomoMorrice]

my thought would be:

If the Staff Member is in cell B3 in Sheet 1 of Workbook A, then wherever you need that information in Workbook B enter: =[WorkbookA.xlsx]Sheet1!$B3 and then copy it down to wherever you need it. This will automatically update whenever both are open and will update when you open Workbook B.

Hope this helps.

Hey Peter,

Nah I'm afraid I encounter a problem that when I go to update Workbook B after closing it and editing cells in Workbook A.

It sasy that there is an error and the links cannot function and therefore it cannot update it.

Any solution to this problem? I appreciate your help! ​

 

[Chris Mack]

Yeah this seems to work. Except it doesn't want to copy down in descending order, just continually stuck with C9 or C10 if I use those cells at the beginning.. Any ideas??

Make sure there are no absolute cell references ("$"s) and make sure you're only selecting one cell when copying down.

Is the problem still there?

 

[pjmorris]

hmmm, not sure about that - fundamentally I think it should work. Have you tried Chris Mack's suggestion to create the formula? (just make sure that the $ signs are in the right place - either select the address and press F4 or edit manually in the formula bar.)

 

[MomoMorrice]

Make sure there are no absolute cell references ("$"s) and make sure you're only selecting one cell when copying down.

Is the problem still there?

That's it now guys, thank you very much!

Just needed to get rid of the exact value $ signs in the formula.

Cheers

 

[Chris Mack]

Yw.