Thread: Dynamic Content in Excel?
2011-12-01, 08:03 #1
- Join Date
- Jan 2011
- Thanked 0 Times in 0 Posts
Dynamic Content in Excel?
I have a number of rows that I want them to be reflected on another sheet, so I am using the paste “link”.
However, this means that I always have to make the changes in the source. I would like to be able to make the changes either in the source or the target. In fact, I don’t care which is source or target.
Is there a way to reproduce the content so that I can do this?
Subscribe to our Windows Secrets Newsletter - It's Free!
Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!
+ Get this BONUS — free!
Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!
2011-12-01, 08:13 #2
- Join Date
- Dec 2009
- Manchester, United Kingdom
- Thanked 15 Times in 14 Posts
My advice would be to decide which is to be the source and which the target. "I don't care which" suggests to me that later "I don't know which" will happen.
I see a lot of spreadsheets where sheet B is supposed to auto-update based on data in sheet A but someone has replaced one or two links with typed values, invalidating all the results. While it could be possible to have a setup where typed values in sheet B also update sheet A it would be very hard to do without creating an infinite loop (A updates B which updates A which...). Unless someone else knows better?
I recommend you should have just one place where data is updated and stick with it.
2011-12-01, 08:42 #3
- Join Date
- Jul 2002
- Pittsburgh, Pennsylvania, USA
- Thanked 331 Times in 324 Posts
I agree with iansavell. It is not worth the effort trying to keep updating 2 separate places. The way that I can imagine doing it would be to NOT use any formulas or links, only values and have a table of which cells from each sheet are supposed to match which cells of the other sheet and then when either was changed, a macro would run, look up its "link" and change that cell.
[Some issues: UNDO would be disabled, and any sorting you would want to do, would require that the link table be updated
You could get around the "link table" if each entry on the rows had a unique key that was present in each set so that when a change were made the unique key could be used to find the appropriate row in the other set and then some "column" mapping to see which column of one sheet associated with the other]
2011-12-01, 11:08 #4
- Join Date
- Mar 2002
- Newcastle, UK
- Thanked 168 Times in 164 Posts
You would also need to be careful to accomodate the different ways cells could be changed.
For example, more than one cell can be changed at one time by using a 'copy and paste', and you could 'blank' a whole chunk of cells by selecting a range and hitting the [Delete] key.
It is possible to use the worksheet change event to detect changes, determine whether the cell(s) that have been changed are relevant to the synchronising requirement, and implement the required changes on the other sheet (turning Events Off whilst making these changes to avoid the infinite loop referred to by iansavell etc).
It can be complicated to do so you need to be sure that you need to do it i.e. there isn't a simpler solution.