• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 7036
  • Last Modified:

Absolute values in Excel

I need to make a list of values in Excel, and then have them be absolute.

For instance, a few cells would be:

=$B$1
=$B$2
=$B$3
=$B$4
....
=$B$50


What is the easiest way to set this up?  Once you put the dollar sign in, you can't easily fill down the list.
P.S. - Don't ask me why they have to be absolute, my boss wants to move stuff around and likes it that way.
0
BOTA-X
Asked:
BOTA-X
  • 3
1 Solution
 
byundtCommented:
Hi BOTA-X,
You can do it with a formula and a macro. The formula might be:
="=" & CELL("address",B1)               when copied down, you get =$B$1,      =$B$2,    etc.

The macro would then be run after you select the cells containing those "formulas". It converts text that looks like a formula into a real formula.

Sub TextToFormulas()
Selection.Formula = Selection.Value
End Sub

To install a sub in a regular module sheet:
1) ALT + F11 to open the VBA Editor
2) Use the Insert...Module menu item to create a blank module sheet
3) Paste the suggested code in this module sheet
4) ALT + F11 to return to the spreadsheet

To run a sub or macro:
5) ALT + F8 to open the macro window
6) Select the macro
7) Click the "Run" button

Optional steps to assign a shortcut key to your macro:
8) Repeat steps 5 & 6, then press the "Options" button
9) Enter the character you want to use (Shift + character will have fewer conflicts with existing shortcuts)
10) Enter some descriptive text telling what the macro does in the "Description" field
11) Click the "OK" button

If the above procedure doesn't work, then you need to change your macro security setting. To do so, open the Tools...Macro...Security menu item. Choose Medium, then click OK.

Cheers!

Brad
0
 
BOTA-XAuthor Commented:
Seems to be working as intended!

Is there a way to make it affect EVERY selected cell?  
0
 
byundtCommented:
The macro works on every cell in the selection. It will wipe out a real formula--so be careful.

To get around that possibility, try the following revision to the original macro:

Sub TextToFormulas()
Dim cel As Range, rg As Range
Set rg = Intersect(Selection, ActiveSheet.UsedRange)

Application.ScreenUpdating = False
On Error Resume Next
For Each cel In rg.Cells
    If Left(cel, 1) = "=" Then cel.Formula = cel.Value
Next
On Error GoTo 0
Application.ScreenUpdating = True
End Sub

Brad
0
 
byundtCommented:
BOTA-X,
Thanks for the grade!
Brad
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now