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!

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.

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.

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

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?

That's everything. Thank you :-)