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.
LVL 8
BOTA-XAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
byundtCommented:
BOTA-X,
Thanks for the grade!
Brad
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.