Merging Excel Info
Thread Starter
Merging Excel Info
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
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
Join Date: Jan 2008
Location: London, England
Age: 56
Posts: 300
Likes: 0
Received 0 Likes
on
0 Posts
Example:
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.
- Create two empty workbooks, BookA.xlsx and BookB.xlsx (Save as... somewhere)
- In BookA enter a value into A1, e.g. "Hello"
- In BookB enter the following into any cell you like : =[BookA.xlsx]Sheet1!$A$1
- Notice that "Hello" appears in your chosen cell
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.
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.
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.
Join Date: Apr 2010
Location: London
Posts: 7,072
Likes: 0
Received 0 Likes
on
0 Posts
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.
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.
Thread Starter
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!
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!
Join Date: Jan 2015
Location: Mars
Posts: 72
Likes: 0
Received 0 Likes
on
0 Posts
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.
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.