Excel help

regulus · 21 Jun 2014 at 04:44

I know you guys get a lot of these but mine should hopefully be simple. It even comes with a picture to help understanding as my explanations sucks.

I've knocked up a simple spreadsheet to help me keep track of my part time trade business. On the one side I've got my orders, amounts, contractor name, etc, on the other side I keep track of my figures. It updates in real time as I add new orders. It shows my profit, tax, what I need to pay in wages and also gives me an overview on a month by month basis. Now my problem is this;

I want to scroll down on the left as I add orders through the month but I want the right side to stay frozen. I can't seem to figure out how to do this. I've split it into panes but it still won't work. I can only seem to freeze the first row of cells or columns but no more. It's not a deal breaker but I want to see the figures update on the right as I add them on the left. It's purely psychological but as I'll start paying out 40-50k a month towards the end of the year, I want to ensure 1000000% I got this stuff down to a tee. When I get to next year and I'm scrolled down way below, I don't want to keep scrolling up to ensure my 'right side' is updating as it should.

I really hope this makes sense.

VapourTrail · 21 Jun 2014 at 08:35

Don't think you can freeze in a vertical manner unless there is some clever VB script. I would suggest either:

1. Drop the left hand table to start below the last line of the right hand table then freeze horizontally below the right hand table. This will allow you to scroll the bottom half which would show the left hand table while leaving the top containing the right hand table in place.

2. Place the right hand table on a separate tab.

Namsnik · 21 Jun 2014 at 10:56

As VapourTrail said but a small tweak:
Use the Camera tool to take a picture of your summary table and put the picture in rows above the input/orders table. The picture will update as you add new orders.
With this solution your summary table is often easier to manage on a different sheet as there you can format rows and columns to best suit that table.

The camera tool is a bit hard to find because it is not in any standard ribbons/toolbars but you can customise the Quick Access Toolbar (or any toolbar in earlier versions of xl) to include it

regulus · 21 Jun 2014 at 22:39

Thanks for the suggestions guys. I'm quite liking the idea of putting the summary box on a different tab. Realised that I should've done it when I was near the end but by then too late. Would there be an easy way of doing it without going through the pain of changing formulas?

izzop · 22 Jun 2014 at 05:49

Just an idea, but why not move the information you currently have to the right so it's at the top of the sheet?

That way you'll be able to freeze this and then when you do scroll down it'll always stay at the top and visible.

As I said, just an idea.......

Regards

VapourTrail · 22 Jun 2014 at 12:09

regulus said:
Thanks for the suggestions guys. I'm quite liking the idea of putting the summary box on a different tab. Realised that I should've done it when I was near the end but by then too late. Would there be an easy way of doing it without going through the pain of changing formulas?

You should be able to either 'cut - paste' or 'copy - paste' the cells you want to move to the new tab. Excel is smart enough to update the formulas. If you just copy then you will have two copies of the data which means you can do some easy testing to ensure that the data on the new tab is updating as required. Once you are happy, simply delete the original

regulus · 24 Jun 2014 at 10:14

I've sucked it up and used two pages. I needed to add more columns for various nonsense my clients require so had to go that way. However, the Camera Tool has been invaluable. My figures I add on the second sheet directly to my summary table I can now see via the camera tool on the sheet I'm working on.

Been struggling for hours getting all the formulas to place nice with each other and I think I'm finally there. In hindsight I should have spent more time on UI design and general layout. It's strange that you never think about that. The scary thing is that I've already come to the end of the extend(ability) of my design. If I had to take on different kinds of contracts now the whole thing will become a complex mess. Or at the very least, very very complicated to maintain and infinitely worse, to trust. Dealing with 26 unique columns that all depend on each other in different ways are no fun