'=' forumla not dragging and updating

[danielewicz]

Hi all,

Thanks for your help. I am assuming this is pretty simple because I've managed to do it before but now excel is denying me the right. Damn!

What's the problem?

I have two sheets. Sheet A is a calendar for 2015 with the dates going from left to right at the top. Sheet B is a 'top-line' view of the calendar which essentially copies key information from sheet A using the simple "=" formula. I'm sure you all know how it works, but basically, I select A1 for example in Sheet B and press =. I then select A1 in sheet A and press enter, and hey presto - Sheet B, A1 shows exactly whatever I type in sheet A, A1.

BUT... I have LOADS of cells covering the entire year. Doing this manually per cell would be insane. So, I thought if I did the first formula then, selected the cell and dragged down or up (using the bottom right little copy handle) it would increase the cells as they should (rather than all cells being just A1). And it worked, so I know have this:

(B)A1 = (A)A1
(B)A2 = (A)A2
(B)A3 = (A)A3

Perfect!

BUT... now I want to drag horizontally, not just vertically. I want to achieve this:

(B)A1 = (A)A1 (B)B1 = (A)B1 (B)C1 = (A)C1
(B)A2 = (A)A2 (B)B2 = (A)B1 (B)C2 = (A)C2
(B)A3 = (A)A3 (B)B3 = (A)B1 (B)C3 = (A)C3

But that doesn't work. Instead, when I drag the other way, I get this:

(B)A1 = (A)A1 (B)B1 = (A)B1 (B)A1 = (A)A1
(B)A2 = (A)A2 (B)B2 = (A)B1 (B)A2 = (A)A2
(B)A3 = (A)A3 (B)B3 = (A)B1 (B)A3 = (A)A3

It's like the pattern isn't being understood? I know it is possible, because I have done it before, but It's simply not doing it now, no matter what I do. I've tried pressing F9, doing a few cells manually so the pattern has more to understand, but then it just goes back to doing the same thing - repeating, instead of increasing.

Does that make sense? Can anyone help?

Thank you so much!

[romperstomper]

You don't have =A!$A1 rather than =A!A1 in the cells on B do you?

[Zodeeak]

Don't drag unless you have more than 1 cell. Do a copy/paste over the area, instead. If you do a simple drag, Excel will copy only what's in the cell, not adjust for cell relativity.

Hope that helps.

-Z

[FDibbins]

Don't drag unless you have more than 1 cell. Do a copy/paste over the area, instead. If you do a simple drag, Excel will copy only what's in the cell, not adjust for cell relativity.

Hope that helps.

-Z

Copy/paste is pretty much the same as dragging. non-absoluted (locked) cells will adjust as needed when either copied or "dragged" down

[danielewicz]

I don't have '$' - I know that locks the formula so it copies it rather than increases it, right?

Copy paste doesn't work for me with one cell that contains the formula. It just copies the formula (B1 = A1) to the rest of the cells. I'll see if I can fill in a few formulas automatically and then copy and paste... will let you know the result!

[FDibbins]

Copy paste doesn't work for me with one cell that contains the formula. It just copies the formula (B1 = A1) to the rest of the cells

If your cells are not absoluted, then if =A1 is in B1, when you copy B1 to B2, then B2 will contain =A2

If you copy B1 to C1, C1 will contain =B1, and C1, copied down will contain B2...

	A	B	C
1		=A1	=B1
2		=A2	=B2

[FDibbins]

hmm wait, just exactly what do you have in A1? Can you upload a sample workbook?

[danielewicz]

Of course - attached. test schedule.xlsx

I have put one formula in already in F3 hopefully that gives you an idea of what I want to do?

Thanks again so much for your help.

[romperstomper]

Your cells are formatted as Text - format them as General and then fill down and right.

[danielewicz]

Your cells are formatted as Text - format them as General and then fill down and right.

That's fixed it! Wow, something so simple - I feel silly asking now!

But thank you, I shall definitely remember this for next time! Excellent help all