Quick Excel Help - Absolute Cell References

[madurobob]

I have a few hundred cells with formulas in them referencing other tabs in the same worksheet. All of them use "relative" cell references rather than "absolute". I need to be able to copy/paste these several times without the formulas changing, so I need the formulas to have absolute cell references.

I know I can edit each cell manually, adding the "$", but it will take forever. I'm pretty sure there is a keystroke combination that will let me toggle between relative and absolute references. Anyone know what it is? Something to do with F4 key?

[biomorph]

Use the F4 key on your keyboard to toggle through the 4 possible choices of absolute and relative
references. When you have the formula you want, press enter

[Wudang]

Select the range first then F2 to go into edit mode then F4 to cycle through

[biomorph]

Select the range first then F2 to go into edit mode then F4 to cycle through

yup that's better/

[madurobob]

Select the range first then F2 to go into edit mode then F4 to cycle through

Hmmmm... not quite. I can select the range. Hitting F2, though, only opens the first cell in the range to edit, then F4 toggles though the cell reference possibilities for that cell only. So, to use the F4 key I still have to open/edit each cell individually.

(I'm using Excel 2002 - maybe different in other versions?)

I could have sworn there was a combination of keystrokes that would do a mass change, but I can't find it. It may have been a macro someone wrote that I saved on an old laptop... or I may simply be wishful remembering!

I can use find/replace to cut down on the volume of editing... but its still a daunting task... 1442 cells to update.

[Robert Oz]

I don't know of a quick way of making a mass change from relative to absolute, but there is a way around it.

Copy the tab that contains all the formulas.
Using this copied tab, instead of copying the formulas you need, cut them.
Paste to the new location.

When you cut and paste formulas in Excel instead of copy and paste, the formula references do not change. You will need to make copies of the tabs for however many copies of the formulas you need. Once you're done, just delete the extra copied tabs.

[varwoche]

I could have sworn there was a combination of keystrokes that would do a mass change, but I can't find it. Ctrl+Enter might be your forgotten keystrokes however this won't work for the problem at hand.

ETA: here (http://www.ozgrid.com/VBA/formula-ref-change.htm) is a utility.

[Wudang]

Apologies - you're right. This macro appears correct from a quick scan. This PC doesn't have excel on it
http://www.ozgrid.com/VBA/formula-ref-change.htm

[madurobob]

I don't know of a quick way of making a mass change from relative to absolute, but there is a way around it.

Copy the tab that contains all the formulas.
Using this copied tab, instead of copying the formulas you need, cut them.
Paste to the new location.

When you cut and paste formulas in Excel instead of copy and paste, the formula references do not change. You will need to make copies of the tabs for however many copies of the formulas you need. Once you're done, just delete the extra copied tabs.
I hadn't thought of that! That will work, and work quickly.

Ctrl+Enter might be your forgotten keystrokes however this won't work for the problem at hand.

ETA: here (http://www.ozgrid.com/VBA/formula-ref-change.htm) is a utility.

Apologies - you're right. This macro appears correct from a quick scan. This PC doesn't have excel on it
http://www.ozgrid.com/VBA/formula-ref-change.htm

Great minds... this macro looks perfect. Will give it a shot after lunch, thanks.

[TriskettheKid]

Depending on what you're doing, you could also do a simple "Find and Replace" using CTRL+H.

The work I do is fairly spreadsheet intensive, and when I'm faced with tons of cells I need changing, I can go the "Find and Replace" route. Assuming, of course, that there is a general amount of uniformity between the cells you're editing.