Excel/Modifying multiple absolute references
QUESTION: Hi Tom,
I have a spreadsheet which has literally hundreds of absolute references, and my question is whether I can copy the existing absolute reference of a cell, then paste as text (WITHOUT THE '=' REFERENCE!) in another cell, and then use a simple qualifier to make a new value for the cell contents.
Here's what I have in two examples, and then the corresponding results I am looking for:
in cell X535:
The absolute reference: =$A$335
to cell Y535:
Copied as text reference: $A$335
New value in cell Z355:
So, summarizing I'd like to be able to use the absolute reference without the equals sign in another cell for updating purposes.
is this feasible sir?
Thanks in anticipation
If I understand the question and what you want to do, then
so if there is a cell address in Y535 ($A$355), then the above formula will display the value in A355
now if you are asking how to get the string $A$355 into cell Y535 from cell X535. Just copy and paste X535 into cell Y535. Then select at least two cells and do a replace
Replace with <nothing>
(if it doesn't work, you probably have whole cell in the options. change that)
If you just select Y535, then the find and replace will work on the whole worksheet. Selecting at least two cells (one can be blank - they don't have to be contiguous) prevents this liberal interpretation.
If I haven't answered your question, just follow-up with where I have misunderstood.
[an error occurred while processing this directive]---------- FOLLOW-UP ----------
QUESTION: Hi Tom,
It's not getting me the desired result, and unless I can figure out a workaround, I may have 1104 manual adjustments to carry out!
All I want to do is change the current formula in a cell, and then incorporating the exact same modification through the 1103 other cells too! Some sort of global updating method !!
I am trying to lose the default zero that appears as a result in X535
I need the formula in Cell X535:
to be changed to
just so I can count the actual zeros from a 2-0 scoreline...
Well, that isn't what you asked in your first question.
To alter the formula within a cell, then you would need a macro to do that.
I selected X535 which had the formula =A355 and ran the below macro and it changed the formula to
You need to put this macro in a standard module. Then select the cells you want altered and run the macro. (You can select more than one cells - all the selected clels will be changed)
Dim cell As Range, s As String
For Each cell In Selection
If cell.HasFormula Then
s = cell.Formula
If Left(s, 1) = "=" Then
s = Mid(s, 2, 255)
cell.Formula = "=IF(" & s & "="""",""""," & s & ")"
Also, you should test this on a copy of your workbook until you are confident it works as it should. I have no way of knowing what your workbook looks like. Things like merged cells could be a problem as an example. If you do run it on your original workbook, you can always close without saving changes if you get into trouble.