Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 07-10-2014, 02:11 AM
paulkaye paulkaye is offline Lock Linked Cells And Unlinked Cells In A Row Windows 7 64bit Lock Linked Cells And Unlinked Cells In A Row Office 2007
Advanced Beginner
Lock Linked Cells And Unlinked Cells In A Row
 
Join Date: Nov 2011
Posts: 70
paulkaye is on a distinguished road
Default Lock Linked Cells And Unlinked Cells In A Row

I have a workbook with two worksheets. In sheet1, column A is a list of files, in a particular order (including gaps between 'groups'). The rest of the columns are details about those files. I'd like to use sheet2 to store a different set of data about the files in column B. Simple linking of cells (eg. putting "=sheet1!A1" into cell A1 of sheet2) displays the list of files on sheet2. However, if I mess with the list on sheet1, I have problems. For example:


- If I insert a row in sheet1, sheet2 doesn't automatically update.
- If I change the order of cells in column A of sheet1, irrespective of whether the order of the cells in column A of sheet2 updates successfully, the cells in column B of sheet2 do not, and I therefore corrupt my data.
I found a similar question (with no answer) here: http://www.ozgrid.com/forum/showthread.php?t=152241

Any help would be most appreciated!
Reply With Quote
  #2  
Old 07-10-2014, 06:40 AM
BobBridges's Avatar
BobBridges BobBridges is offline Lock Linked Cells And Unlinked Cells In A Row Windows 7 64bit Lock Linked Cells And Unlinked Cells In A Row Office 2010 32bit
Expert
 
Join Date: May 2013
Location: USA
Posts: 685
BobBridges has a spectacular aura aboutBobBridges has a spectacular aura about
Default

Let me start here, paulkaye: Why do you want the data describing the files to be on two separate worksheets?

Don't misunderstand me; there are plenty of reasons it'd be nice, starting with "just because I want to". But you're running into the difficulty that poses. I'm sure there are ways around it, but maybe it's simplest to start with a master sheet having all the data in one place and then figure out how to display selected columns elsewhere. Before putting much work into a workaround, I want to start by examining the basic layout.
Reply With Quote
  #3  
Old 07-10-2014, 10:53 AM
paulkaye paulkaye is offline Lock Linked Cells And Unlinked Cells In A Row Windows 7 64bit Lock Linked Cells And Unlinked Cells In A Row Office 2007
Advanced Beginner
Lock Linked Cells And Unlinked Cells In A Row
 
Join Date: Nov 2011
Posts: 70
paulkaye is on a distinguished road
Default

Hi BobBridges,
That's actually a really great question in response. I'd kind of decided that if I'm unable to make this work reliably, that I would in fact put all the data on one sheet. The reasons are:
1) The sheets are for different purposes, and would be used in different contexts.
2) The data that I'm hoping to put into column B of sheet2 is going to be long(ish) strings of text, preferably with line breaks. If it has to go into sheet1, it'll make the whole sheet very bulky and less readable than it currently is.

Your suggestion "to start with a master sheet having all the data in one place and then figure out how to display selected columns elsewhere" sounds likely to be a good way forward. It'll solve reason (1) above, but can you think of how to solve reason (2) that way?

Thanks in advance.
Reply With Quote
  #4  
Old 07-14-2014, 07:50 AM
paulkaye paulkaye is offline Lock Linked Cells And Unlinked Cells In A Row Windows 7 64bit Lock Linked Cells And Unlinked Cells In A Row Office 2007
Advanced Beginner
Lock Linked Cells And Unlinked Cells In A Row
 
Join Date: Nov 2011
Posts: 70
paulkaye is on a distinguished road
Default

Solved it!

I place a semicolon within data3 each time there should be a new line. I enter data3 in this way on one line in sheet1, and use the SUBSTITUTE function in combination with CHAR(10) to display data3 on multiple lines in sheet2.

Paul
Reply With Quote
  #5  
Old 07-14-2014, 10:15 AM
BobBridges's Avatar
BobBridges BobBridges is offline Lock Linked Cells And Unlinked Cells In A Row Windows 7 64bit Lock Linked Cells And Unlinked Cells In A Row Office 2010 32bit
Expert
 
Join Date: May 2013
Location: USA
Posts: 685
BobBridges has a spectacular aura aboutBobBridges has a spectacular aura about
Default

Without seeing it for myself I can't be sure, but this sounds like a good solution, paulkaye. Does that take care of everything or are there more questions?
Reply With Quote
  #6  
Old 07-14-2014, 10:20 PM
paulkaye paulkaye is offline Lock Linked Cells And Unlinked Cells In A Row Windows 7 64bit Lock Linked Cells And Unlinked Cells In A Row Office 2007
Advanced Beginner
Lock Linked Cells And Unlinked Cells In A Row
 
Join Date: Nov 2011
Posts: 70
paulkaye is on a distinguished road
Default

That's everything. Thank you :-)
Reply With Quote
Reply

Tags
alignment, linking, worksheet

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Update Link very slow -PowerPoint Charts & object, cells etc Linked to Excel johnseito PowerPoint 0 01-28-2014 06:24 PM
Lock Linked Cells And Unlinked Cells In A Row How to lock only some cells in 2010 ? spookiepower Word 4 11-26-2013 02:40 PM
linked cells msheyworth Excel 5 04-03-2012 03:12 PM
placing linked Excel cells in Word next to a chart Paul-NYS Word 4 03-16-2012 12:59 PM
Protect / Lock Cells / Print Set-up? meggenm Excel 3 01-26-2012 09:57 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 11:05 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2022, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2022 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft