Split Worksheet Concept

Have been asked this question at work, and as usual do not know the answer:

Is it possible in Excel to 'split' a worksheet such that part of it can be controlled independently from the rest ?

What the questioner wishes to do is as follows:

Keep cols A:D separate from E:onwards (for display purposes cols E:N)

They will do calculations on data they enter in cols A:D and this might extend down many rows
say to row 100. The result might go into cols E:N, but in say rows 5-6.

They wish to keep (say) the range(E1:N10) visible, whilst entering data in cols A:D down to row 100.

It's kind of like having a split screen with two sheets displayed.

My best suggestion to them (since there is only one video card and one screen) was to open two instances of
Excel, and a separate workbook loaded into each instance.
That way they would have two sheets visible side-by-side, with independent scrollbars.

I'd just have to figure out how to code such that their results from the calculations done in the lefthand sheet get transferred into the righthand sheet. and there would need to be links to and from each workbook/sheet to show what came from where.

I'm sure it could be done, but I'm wondering if it would be better to display a userform alongside their sheet, and use the form for the calculations.

Unsure about the best approach, here, so your advice would be much appreciated.

Regards and thanks.

Hi ,

Thanks for the reply.

Unless I've misunderstood your suggestion, I did say:

"My best suggestion to them (since there is only one video card and one screen)"

So options seem a bit limited, to me.

Thanks.

Not sure I'm understanding this correctly - but have you seen the under the "View" menu - "View Side by Side" which allows you to view multiple workbooks at the same time and lock / unlock scrolling...
... otherwise there's the "Split" and then the "Freeze panes" option?

+1 for Freeze Panes.

If that doesn't do what you need you can try something like the following (it's a bit messy though...) It basically takes a picture of the range you want to see and then pastes it in the top left visible cell. You won't be able to click on any cells underneath the picture.

Note the code only runs when the selection is changed: i.e. when a cell is clicked - scrolling won't do it.

Please Login or Register  to view this content.

Many thanks all for the many suggestions - much appreciated.

I think I'll propose protonLeah's suggestion.

However, developing this approach, I have a couple of questions, since I think I can guess what my colleague will be looking for next:

1. Could a "master" XLW file be created - similar to an XLT file ?

2. Can the XLW file accept VBA modules, forms etc ?

It will be useful for them to be able to call up a 'blank' "template" for the start of a new project.

In addition, I think I'll end up having to run code on the user's data in cols "A:D" to produce a numeric value in (say) a Total cell, that is then transferred into the appropriate cell in col G.

Regards, and thanks all.

Thanks for the clarification and suggestion.

Regards.