Merging Excel Info

[gehenna]

Morning Everyone

May I ask for some help regarding MS Excel.

I have 2 seperate account sheets, and would like to find a way of automatically copying information entered in say sheet A into sheet B, without my having to do the work twice.

Thanks, as always

G

[MacBoero]

Example:

1. Create two empty workbooks, BookA.xlsx and BookB.xlsx (Save as... somewhere)
2. In BookA enter a value into A1, e.g. "Hello"
3. In BookB enter the following into any cell you like : =[BookA.xlsx]Sheet1!$A$1
4. Notice that "Hello" appears in your chosen cell

In step 3, if BookB isn't in the same folder as BookA, Excel may ask you locate the workbook using a file chooser dialogue.
If you change the location of a workbook, when opening a workbook that refers to it (e,g, BookA is moved and then opening BookB) if Excel doesn't prompt you to automatically, you may need to update the file links, using Data > Connections > Edit Links. This dialogue will allow you to check the status and update/refresh the links between workbooks.

[gehenna]

MacBoero

Thank you very much for that information.

I shall put it all together this evening, and appreciate your time.

[India Four Two]

You can make the creation of the formula easier, by typing = in the destination cell (in workbook B) and then clicking in the appropriate cell in workbook A and then pressing Enter/Return.

If you remove the $ characters from the formula, you can then copy the formula into all the other cells in workbook B where you need copies of data in workbook A.

Caveat: Based on years of experience, I don't recommend having formulae which reference other workbooks. There is too much scope for things to go badly wrong. My preference would be to have the data in two sheets in the same workbook.

[Heathrow Harry]

Originally Posted by India Four Two

You can make the creation of the formula easier, by typing = in the destination cell (in workbook B) and then clicking in the appropriate cell in workbook A and then pressing Enter/Return.

If you remove the $ characters from the formula, you can then copy the formula into all the other cells in workbook B where you need copies of data in workbook A.

Caveat: Based on years of experience, I don't recommend having formulae which reference other workbooks. There is too much scope for things to go badly wrong. My preference would be to have the data in two sheets in the same workbook.

I was advised , in complex linked sheets, to always have sheet 1 as a summary . Then change a critical input eg double the cost.. and see if it makes a change you'd expect in the final output... saved the bacon a few times.....

[gehenna]

Thank you India Four Two for your good advice.

Heathrow Harry, Thank You also for your useful comments.

All advice is gratefully received and digested, and hopefully when HRH has allowed me some free time, I'll sort it out!

[Lascaille]

Just to add a word of warning, the procedure above (linking cells by a =Sheet1! style reference) doesn't copy the data from the cell in Sheet1 to the cell in Sheet2 but establishes a constantly-updating link between the cells.

That means every time you open Sheet2, it opens Sheet1 and updates itself according to the current values of Sheet1.

That means, if you mangle Sheet1 and somehow replace a bunch of historical data with zeroes, those changes will be copied into Sheet2 the next time you open it.

[gehenna]

Lascaille

Thanks for the warning! I could well have fallen into that trap, but will now have to be that bit more careful.

[Jhieminga]

You could create a macro that copies entries into a second sheet, but using just formulas, this is the best that's possible I'm afraid.

[gehenna]

Jhieminga

Thanks also for your input. Looks as if the weekend will be the time to put everyone's good advice to use.

g