Excel question

  exdragon 18:06 27 May 04
Locked

Reposted with the correct title of Excel question, not email...

Hi, can someone tell me how to do the following, please?

Sheet 1 contains 2 columns for each month, sheet 2 has 1 column for each month. This is linked to the first sheet, and I want to add both totals from sheet 1 to appear as a single total on the second sheet. But, if I format sheet 2 as col B to equal sheet 1 col A + B, then drag across sheet 2, I get on sheet 2 A+B, then B+C, C+D etc, instead of A+B, C+D, E+F.

Bit complicated to explain, but I've got a feeling brackets come into it somewhere!

Thanks in advance

  Eric10 18:12 27 May 04

If you put a $ sign in front of your Column reference as in $A1+$B1 then they will stay as columns A+B when the formula is copied or dragged.

  exdragon 19:09 27 May 04

Thanks, but I don't think that works. I don't want A and B to be absolute, I want A+B (from sheet 1) to be displayed in cell A on sheet 2, followed by C+D (from sheet 1) in cell B on sheet 2.

If I make A & B absolute, sheet 2 has A+B, B+C and C+D in the first 3 columns.

Any other ideas??

  Megatyte 19:35 27 May 04

In the target cell on sheet 2 type...

=SUM(Sheet1!A:A)+SUM(Sheet1!B:B)

AH

  exdragon 19:51 27 May 04

Sorry, Megatyte, this still gives me A+B, B+C etc when I copy the formula across

  Muckle 20:05 27 May 04

If you manually enter the correct formula into column A (=SUM(Sheet1!A:A)+SUM(Sheet1!B:B)) and B (=SUM(Sheet1!C:C)+SUM(Sheet1!D:D))on Sheet 2, select both cells and then click and drag to replicate the formula, it should work.

  Megatyte 20:14 27 May 04

I see what you mean now.

One way of doing it is to hide every other column on sheet 2, using columns A,C,E etc. When you drag across them then the hidden column B will contain B+C and column C will contain C+D and so on across the range.

AH

  Simsy 20:19 27 May 04

I am going to assume that the 2 colums on the first sheet are A and B, and the column on the second sheet is C...

I'm also assuming that we are starting with Row 1 in each case...

and just to be doubly sure, I think what you want to achieve is that in Sheet 2 Cell C1, you want to have the sum of Sheet 1 Cell A1 plus Sheet 1 Cell B1.... and then on sheet 2 you want to copy the formula in cell C1 down....

Sorry that's so long winded, but if I've understood the problem correctly here's how I'd do it...

In Sheet2 cell C1 type the following;

=SUM(

Note just the opening bracket... without clicking anywhere else now click the sheet1 tab at the bottom... this opens sheet1... now select sheet1 cell A1 and drag across to include Sheet1 Cell B1... you should have the"marching ants" round the two cells.... now press "Enter" on the keyboard.

This should take you back to sheet2 and you should see the following entered as the formula;

=SUM(Sheet1!A1:B1

Now you need to add the closing bracket and press return.

Now you can copy that formula down the column.

I hope that's what you wanted... apologies if it isn't!

Regards,

Simsy

  Simsy 20:34 27 May 04

that I missed out the second part...

What I've done is get you Rows A and B added...

Just repeat the process for Sheet 2 Cell B2...

Ultimately, the formula you need in Sheet2 CellA1 is;

=SUM(Sheet1!A1:B1)

and the formula you need in Sheet2 Cell B1 is:

=SUM(Sheet1!C1:D1)


In both these cases the formula can be dragged down the repective columns.

I can't see a way of dragging the formula from Sheet2 Cell A1 across to Sheet2 Cell B1...

However, if you drag Sheet2 Cell A1 across 2 cells,, you should have a similar formula in Sheet 2 B1, and Sheet2 C1.... This formula in sheet2 C1 you can move one cell to the left so it is now in Sheet2 B1. (You may get a "Do you want to replace... bla bla" warning... just say yes!)

I hope that make sense!

Regards,

Simsy

  exdragon 21:22 27 May 04

Muckle, I wonder if there's something wrong with my excel - when I type in the formula and press return, it opens the My Documents folder, with a File not Found heading...

Megatyte, I had that sort of idea, but if I put (as an example)on sheet 1, 100 in A & B, 200 in C & D, 300 in E&F then link the sheets and drag the cells across on sheet2, I get in 100 in A, 200 in C, 300 in E and 0 in G. However, the formula bar at the top shows the correct formula - ie, in A, it's =Sheet2!A1:B1, while C is =Sheet2!C1:D1

Simsy, I think I'll leave that til the morning!

Do you think I'd better give up on this one?

  Eric10 21:34 27 May 04

Not an actual solution but a possible work around. On sheet 2 insert a blank column between each of your month columns so that A, C, E, G, etc are your months and B, D, F, H, etc are blank. Enter your formula =SUM(Sheet1!A2:Sheet1!B2) into A2 (I've got headings in row 1). Drag the formula across the sheet and then delete your intermediate columns.

This thread is now locked and can not be replied to.

Surface Pro (2017) vs Surface Pro 4

20 groundbreaking 3D animation techniques

How to mine Bitcoin on Mac