Many sheets, one DB?

[Garrett Gaston]

I deal with a lot of Excel files on a daily basis and I need some help. I may have 8 fields (columns) in one file or sheet, let's say file/sheet A, but I may need some of the same data (rows) in another file/sheet, let's say file/sheet B, but maybe only using five of the original fields in file/sheet A and maybe only rows where field ABC = XYZ. I'm currently having to edit more than one file or sheet with mostly the same information on a daily basis and I know there's got to be a better way. Each time I enter the same information I'm giving myself the opportunity to make a mistake, I'd like to be able to insert, drop, and edit changes in one file or sheet and have the other files/sheets act as little more than displays for the main data sheet. I think the current version of Excel I'm using is a little dated, 2010 I think but I'm not sure. If you have a solution please let me know even if it will only works in new version as I might be able to get my employer's IT supervisor to upgrade my Office edition. Thanks for any and all replies.

 

[Becky]

Welcome to the forum Garrett

It is indeed possible to have linked data between different spreadsheets. You need to have both the spreadsheets open, and in the cell you want to insert the data from another sheet type the equals sign (=) and the select the cell in the other spreadsheet, and press enter. The cell with the linked data should then look like this:

='[Spreadsheet.xlsx]Sheet1'!$A$1

This shows that the cell is linked to cell A1 in Sheet 1 of Spreadsheet.xlsx

If you want to copy a range of cells - for example, A1 to A5 - then delete the dollar signs and you will be able to drag the formula down as appropriate.

Obviously it means that you need to be careful if multiple people are accessing the spreadsheets etc. Also if you change the file name of the linked spreadsheet, or change its location, that will throw up errors.