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 :-)