Using absolute cell refernce and inserting rows

[Guest]

I am trying to use an absolute cell refernce on tab 2 that refernces tab 1.
When I insert a row on tab 1 (tab name is week 1)the refernce changes. For
example cell A4 the formula is ='week1'!$a$4
when I insert a row on the week 1 tab it changes tab 2 formula for cell A4 to
='week1'!$a$5

What can I do to make it not make that change but still be able to insert
rows?

 

[Guest]

try the indirect function
=indirect("'week1'!$A$4)

 

[JE McGimpsey]

One way:

=INDIRECT("'week1'!A4")

 

[Guest]

Great...that definatly did the trick!!
Now is there a macro to add this automatically for a group of cells?

 

[JE McGimpsey]

What do you mean by "add this automatically for a group of cells"? Would
all of the cells have the same formula? In that case just select the
cells and use CTRL-Enter. If not, how should the formulae be constructed?

Note: It's definitely easier to figure out what you're referring to when
you add your follow-up to the reply, not the original question.

 

[Guest]

Sorry for the confusion...first time using these boards....
What I am trying to do is to update my absolute cells in about 8 columns and
600 rows to include the instructions given to me in a reply to my earlier
post...the reply said to change the cell formula to be:
=INDIRECT("'week1'!$A$4")
but I will need to do this for cells A1 thru L600
I had read in a different post a quick macro to insert the $ making the
cells absolute and was looking for a similar one to insert the new
text(indirect(").

 

[JE McGimpsey]

One way:

Select the cells you want to change, then run:

Public Sub WrapIndirect()
Dim rCell As Range
Dim rFormulae As Range
On Error Resume Next
Set rFormulae = Selection.SpecialCells(xlCellTypeFormulas)

If Not rFormulae Is Nothing Then
For Each rCell In rFormulae
With rCell
.Formula = "=INDIRECT(""" & Mid(.Formula, 2) & """)"
End With
Next rCell
End If
On Error GoTo 0
End Sub


Note that this provides no error checking as to whether the formulas are
of the form you specified (hence the use of Selection).

 

[Gord Dibben]

Lady

Maybe............

Sub Indirect_Add()
Dim myStr As String
Dim cel As Range
For Each cel In Selection
If cel.HasFormula = True Then
If Not cel.Formula Like "=INDIRECT(*" Then
myStr = Right(cel.Formula, Len(cel.Formula) - 1)
cel.Value = "=INDIRECT(" & myStr & ")"
End If
End If
Next
End Sub


Gord Dibben Excel MVP

 

[Guest]

OK I think I am getting closer but I think from this macro is missing two
extra " (quotes) one is after the =indirect(
the other is at the end before the last )

I appreciate everyones help!!

 

[Gord Dibben]

Lady

Ammended or use the one JE posted.

Sub Indirect_Add()
Dim myStr As String
Dim cel As Range
For Each cel In Selection
If cel.HasFormula = True Then
If Not cel.Formula Like "=INDIRECT(*" Then
myStr = Right(cel.Formula, Len(cel.Formula) - 1)
cel.Value = "=INDIRECT(" & """" & myStr & """" & ")"
End If
End If
Next
End Sub


Gord

 

[Gord Dibben]

Ammended further..

Sub Indirect_Add()
Dim myStr As String
Dim cel As Range
For Each cel In Selection
If cel.HasFormula = True Then
If Not cel.Formula Like "=INDIRECT(*" Then
myStr = Right(cel.Formula, Len(cel.Formula) - 1)
cel.Value = "=INDIRECT(""" & myStr & """)"
End If
End If
Next
End Sub


Gord