Formula will not increase with pasting down rows

[T0b3rm0ry]

I have recently upgraded to office 12 which seems to calculate things quite differently. I have managed to fix the "manual calculation thing to automattic" & can't understand why that is the default but have one I can't find the settings for.

For this very basic string:
='\\WAREHOUSEPC\Ostendo\[ItemMasterImport.XLS]ItemMasterImport'!$H$2
I used to be able to paste the same into the next cell down then highlight the top two columns and drag down so that the formula increased by one for each row. Now it just repeats the same two over & over which makes no sense as the the reasoning by me. how do i change the settings so the formula will increase by one with each row?

 

[Michael M]

formula

Hi
do you mean it refers to $H$2 all the way down.
If so, Highlight the $H$2 part of the formula and press F4 until it looks
like H2.
Now try dragging it down and it will change for each row.

HTH
Michael M

 

[Smitty]

Welcome to the Board!

When you paste a link from an external workbook Excel automatically forces the cell reference to Absolute (the $'s). Just remove them to make them relative before you copy/paste. Check out the helpfile for "switch between relative, absolute, and mixed references".

Hope that helps,

Smitty

 

[T0b3rm0ry]

didn't work

Yes in the last version i had if I highlighted the cell & dragged down it would refer to H2 in each cell but if I did H2 then changed the cell below to H3 & selected both cells then dragged down it would continue with 4,5,6, & so on. Now it does h2, h3, h2, h3 & so on. The same happens if I make $h$2 look like h2.

 

[T0b3rm0ry]

This has still given the same results when I drag the formula down. Does it differ that the sheet is on another computer in my domain?

Welcome to the Board!

When you paste a link from an external workbook Excel automatically forces the cell reference to Absolute (the $'s). Just remove them to make them relative before you copy/paste. Check out the helpfile for "switch between relative, absolute, and mixed references".

Hope that helps,

Smitty

 

[Smitty]

Does it differ that the sheet is on another computer in my domain?

Nope.

But I think I know what you're talking about now as I just tested it between both versions. Instead of copying the H2 & H3 referenced cells, just copy the H2 cell and drag down. Sorry for not getting that earlier, but I've only just upgraded to 12 at home in the past few weeks.

Smitty

 

[T0b3rm0ry]

Nope.

But I think I know what you're talking about now as I just tested it between both versions. Instead of copying the H2 & H3 referenced cells, just copy the H2 cell and drag down. Sorry for not getting that earlier, but I've only just upgraded to 12 at home in the past few weeks.

Smitty

That has done the trick, thanks for the help there seem to be a few quirks that are not so helpfull in version 12 when used to 11...