#1
|
|||
|
|||
linking cells between files when cell address changes
I need to know how to keep information in cells from one file linked to another file when the address for that cell changes. In other words, if J34 in file A has information linked to F29 in File B, and because I had to insert a new row of information in File A, J34 now becomes J35, I would like F29 in File B to stay linked to the File A cell that is now J35. In our costing system (which I am new to--I'm also new to Excel), we have multiple files with linked information, but right now any time new information is added (because a new piece of inventory has been added), it throws off the whole system, and it is almost impossible to track every place where the information may have changed. Making information flow even when cell addresses change is crucial--if it can be done. Any advice will be greatly appreciated. Thanks.
|
#2
|
|||
|
|||
I don't think that is possible, but there are other things you can do to get the expected result. For this, please upload a sample of your files, to see what type of info are you trying to get from other file. Basically, it is just a matter of data organisation.
|
#3
|
|||
|
|||
I suppose you are right, but I hope not. I really can't attach any files that would reveal the complexity. There are hundreds of part numbers, and periodically, part numbers are added (not in sequence), and it makes the most sense to insert them in between other part numbers. But when that is done, it changes the cell addresses of all cells below. And those cells are linked to 6 or 7 other excel files that use their data. I was really hoping to be able to keep the accurate information flowing even when cell addresses change. If you or anyone else has any suggestions, it would be so helpful.
|
#4
|
|||
|
|||
Changing the cell "name"?
Will changing the cell "name" from an address to a "name" cause the information to flow through even when the cell address changes? This may be elementary to most of you, but I really am a novice and would like to know if this can work. Thanks.
|
#5
|
|||
|
|||
Both files A and B must be opened;in source file B, place the cursor in cell F29, go to the Insert menu, then choose Name, and then Define from the expanded menu . Enter a name, like F29FileB (no spaces), ( in refers to box you will already have =Sheet1!$F$29 (assuming that the source cell F29 is in sheet1). press enter. We are done with source file.
Now, in destination file A, define another name , could be the same from source: F29FileB, clear everything there is in the refers to box, then switch windows to source file, click on cell F29; now, in refers to box, you will have ='[File B.xls]Sheet1'!$F$29 which must be edited to the name defined in source: ='File B.xls'!F29FileB (or , from the insert menu , Name-use in formula, i think this is the step in your version of office). At this moment, in the destination file A, on a cell you can type =F29FileB then press enter (use it in formulas, like =$C$2+F29FileB ). You will have the value from fila B in that cell, which will follow that cell, no matter how many rows you insert in source. |
#6
|
|||
|
|||
Thanks. I appreciate it.
|
#7
|
|||
|
|||
Just a thought, but wouldn't it just be easier and wiser to have a worksheet called "Absolutes" to link into
This way, your other workbooks and worksheets will reference the same location, while you insert and delete rows without worrying about the location of your Absolutes located in one referencing worksheet usable with all workbooks For example, a tax rate that several cells all over the place uses will always stay correct in its Reference to a tax rate or any other things that never change, or than, say, the rate from 5% to 6% If so, you go to Absolutes, make the change(s) and move on knowing all references to the tax rate has been updated |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Linking Word files | Aitch | Office | 1 | 12-29-2011 03:12 AM |
Naming a cell then linking with a certain amount of charachters looses content. | shabbaranks | Excel | 1 | 12-27-2011 01:06 AM |
Make Cells Mimic The Formatting Of One Cell | skoz55 | Excel | 1 | 11-09-2011 09:00 AM |
Linking text from an embedded sreadsheet cell | spheon | Word | 2 | 05-26-2011 01:03 PM |
How to combine different cells data in one cell? | Learner7 | Excel | 1 | 07-28-2010 12:07 PM |