software

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?try the indirect function
=indirect(quot;'week1'!$A$4)

quot;ladyhawkquot; wrote:

gt; I am trying to use an absolute cell refernce on tab 2 that refernces tab 1.
gt; When I insert a row on tab 1 (tab name is week 1)the refernce changes. For
gt; example cell A4 the formula is ='week1'!$a$4
gt; when I insert a row on the week 1 tab it changes tab 2 formula for cell A4 to
gt; ='week1'!$a$5
gt;
gt; What can I do to make it not make that change but still be able to insert
gt; rows?
gt;

One way:

=INDIRECT(quot;'week1'!A4quot;)

In article gt;,
quot;ladyhawkquot; gt; wrote:

gt; I am trying to use an absolute cell refernce on tab 2 that refernces tab 1.
gt; When I insert a row on tab 1 (tab name is week 1)the refernce changes. For
gt; example cell A4 the formula is ='week1'!$a$4
gt; when I insert a row on the week 1 tab it changes tab 2 formula for cell A4 to
gt; ='week1'!$a$5
gt;
gt; What can I do to make it not make that change but still be able to insert
gt; rows?

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

quot;ladyhawkquot; wrote:

gt; I am trying to use an absolute cell refernce on tab 2 that refernces tab 1.
gt; When I insert a row on tab 1 (tab name is week 1)the refernce changes. For
gt; example cell A4 the formula is ='week1'!$a$4
gt; when I insert a row on the week 1 tab it changes tab 2 formula for cell A4 to
gt; ='week1'!$a$5
gt;
gt; What can I do to make it not make that change but still be able to insert
gt; rows?
gt;

What do you mean by quot;add this automatically for a group of cellsquot;? 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.In article gt;,
quot;ladyhawkquot; gt; wrote:

gt; Great...that definatly did the trick!!
gt; Now is there a macro to add this automatically for a group of cells?
gt;
gt; quot;ladyhawkquot; wrote:
gt;
gt; gt; I am trying to use an absolute cell refernce on tab 2 that refernces tab 1.
gt; gt; When I insert a row on tab 1 (tab name is week 1)the refernce changes. For
gt; gt; example cell A4 the formula is ='week1'!$a$4
gt; gt; when I insert a row on the week 1 tab it changes tab 2 formula for cell A4
gt; gt; to
gt; gt; ='week1'!$a$5
gt; gt;
gt; gt; What can I do to make it not make that change but still be able to insert
gt; gt; rows?
gt; gt;

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(quot;'week1'!$A$4quot;)
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(quot;).

quot;JE McGimpseyquot; wrote:

gt; What do you mean by quot;add this automatically for a group of cellsquot;? Would
gt; all of the cells have the same formula? In that case just select the
gt; cells and use CTRL-Enter. If not, how should the formulae be constructed?
gt;
gt; Note: It's definitely easier to figure out what you're referring to when
gt; you add your follow-up to the reply, not the original question.
gt;
gt;
gt; In article gt;,
gt; quot;ladyhawkquot; gt; wrote:
gt;
gt; gt; Great...that definatly did the trick!!
gt; gt; Now is there a macro to add this automatically for a group of cells?
gt; gt;
gt; gt; quot;ladyhawkquot; wrote:
gt; gt;
gt; gt; gt; I am trying to use an absolute cell refernce on tab 2 that refernces tab 1.
gt; gt; gt; When I insert a row on tab 1 (tab name is week 1)the refernce changes. For
gt; gt; gt; example cell A4 the formula is ='week1'!$a$4
gt; gt; gt; when I insert a row on the week 1 tab it changes tab 2 formula for cell A4
gt; gt; gt; to
gt; gt; gt; ='week1'!$a$5
gt; gt; gt;
gt; gt; gt; What can I do to make it not make that change but still be able to insert
gt; gt; gt; rows?
gt; gt; gt;
gt;

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 = quot;=INDIRECT(quot;quot;quot; amp; Mid(.Formula, 2) amp; quot;quot;quot;)quot;
End With
Next rCell
End If
On Error GoTo 0
End SubNote that this provides no error checking as to whether the formulas are
of the form you specified (hence the use of Selection).

In article gt;,
quot;ladyhawkquot; gt; wrote:

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

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 quot;=INDIRECT(*quot; Then
myStr = Right(cel.Formula, Len(cel.Formula) - 1)
cel.Value = quot;=INDIRECT(quot; amp; myStr amp; quot;)quot;
End If
End If
Next
End SubGord Dibben Excel MVP

On Tue, 13 Dec 2005 08:16:02 -0800, quot;ladyhawkquot;
gt; wrote:

gt;Sorry for the confusion...first time using these boards....
gt;What I am trying to do is to update my absolute cells in about 8 columns and
gt;600 rows to include the instructions given to me in a reply to my earlier
gt;post...the reply said to change the cell formula to be:
gt;=INDIRECT(quot;'week1'!$A$4quot;)
gt; but I will need to do this for cells A1 thru L600
gt;I had read in a different post a quick macro to insert the $ making the
gt;cells absolute and was looking for a similar one to insert the new
gt;text(indirect(quot;).
gt;
gt;quot;JE McGimpseyquot; wrote:
gt;
gt;gt; What do you mean by quot;add this automatically for a group of cellsquot;? Would
gt;gt; all of the cells have the same formula? In that case just select the
gt;gt; cells and use CTRL-Enter. If not, how should the formulae be constructed?
gt;gt;
gt;gt; Note: It's definitely easier to figure out what you're referring to when
gt;gt; you add your follow-up to the reply, not the original question.
gt;gt;
gt;gt;
gt;gt; In article gt;,
gt;gt; quot;ladyhawkquot; gt; wrote:
gt;gt;
gt;gt; gt; Great...that definatly did the trick!!
gt;gt; gt; Now is there a macro to add this automatically for a group of cells?
gt;gt; gt;
gt;gt; gt; quot;ladyhawkquot; wrote:
gt;gt; gt;
gt;gt; gt; gt; I am trying to use an absolute cell refernce on tab 2 that refernces tab 1.
gt;gt; gt; gt; When I insert a row on tab 1 (tab name is week 1)the refernce changes. For
gt;gt; gt; gt; example cell A4 the formula is ='week1'!$a$4
gt;gt; gt; gt; when I insert a row on the week 1 tab it changes tab 2 formula for cell A4
gt;gt; gt; gt; to
gt;gt; gt; gt; ='week1'!$a$5
gt;gt; gt; gt;
gt;gt; gt; gt; What can I do to make it not make that change but still be able to insert
gt;gt; gt; gt; rows?
gt;gt; gt; gt;
gt;gt;

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

I appreciate everyones help!!

quot;Gord Dibbenquot; wrote:

gt; Lady
gt;
gt; Maybe............
gt;
gt; Sub Indirect_Add()
gt; Dim myStr As String
gt; Dim cel As Range
gt; For Each cel In Selection
gt; If cel.HasFormula = True Then
gt; If Not cel.Formula Like quot;=INDIRECT(*quot; Then
gt; myStr = Right(cel.Formula, Len(cel.Formula) - 1)
gt; cel.Value = quot;=INDIRECT(quot; amp; myStr amp; quot;)quot;
gt; End If
gt; End If
gt; Next
gt; End Sub
gt;
gt;
gt; Gord Dibben Excel MVP
gt;
gt; On Tue, 13 Dec 2005 08:16:02 -0800, quot;ladyhawkquot;
gt; gt; wrote:
gt;
gt; gt;Sorry for the confusion...first time using these boards....
gt; gt;What I am trying to do is to update my absolute cells in about 8 columns and
gt; gt;600 rows to include the instructions given to me in a reply to my earlier
gt; gt;post...the reply said to change the cell formula to be:
gt; gt;=INDIRECT(quot;'week1'!$A$4quot;)
gt; gt; but I will need to do this for cells A1 thru L600
gt; gt;I had read in a different post a quick macro to insert the $ making the
gt; gt;cells absolute and was looking for a similar one to insert the new
gt; gt;text(indirect(quot;).
gt; gt;
gt; gt;quot;JE McGimpseyquot; wrote:
gt; gt;
gt; gt;gt; What do you mean by quot;add this automatically for a group of cellsquot;? Would
gt; gt;gt; all of the cells have the same formula? In that case just select the
gt; gt;gt; cells and use CTRL-Enter. If not, how should the formulae be constructed?
gt; gt;gt;
gt; gt;gt; Note: It's definitely easier to figure out what you're referring to when
gt; gt;gt; you add your follow-up to the reply, not the original question.
gt; gt;gt;
gt; gt;gt;
gt; gt;gt; In article gt;,
gt; gt;gt; quot;ladyhawkquot; gt; wrote:
gt; gt;gt;
gt; gt;gt; gt; Great...that definatly did the trick!!
gt; gt;gt; gt; Now is there a macro to add this automatically for a group of cells?
gt; gt;gt; gt;
gt; gt;gt; gt; quot;ladyhawkquot; wrote:
gt; gt;gt; gt;
gt; gt;gt; gt; gt; I am trying to use an absolute cell refernce on tab 2 that refernces tab 1.
gt; gt;gt; gt; gt; When I insert a row on tab 1 (tab name is week 1)the refernce changes. For
gt; gt;gt; gt; gt; example cell A4 the formula is ='week1'!$a$4
gt; gt;gt; gt; gt; when I insert a row on the week 1 tab it changes tab 2 formula for cell A4
gt; gt;gt; gt; gt; to
gt; gt;gt; gt; gt; ='week1'!$a$5
gt; gt;gt; gt; gt;
gt; gt;gt; gt; gt; What can I do to make it not make that change but still be able to insert
gt; gt;gt; gt; gt; rows?
gt; gt;gt; gt; gt;
gt; gt;gt;
gt;

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 quot;=INDIRECT(*quot; Then
myStr = Right(cel.Formula, Len(cel.Formula) - 1)
cel.Value = quot;=INDIRECT(quot; amp; quot;quot;quot;quot; amp; myStr amp; quot;quot;quot;quot; amp; quot;)quot;
End If
End If
Next
End SubGord

On Tue, 13 Dec 2005 13:42:33 -0800, quot;ladyhawkquot;
gt; wrote:

gt;OK I think I am getting closer but I think from this macro is missing two
gt;extra quot; (quotes) one is after the =indirect(
gt;the other is at the end before the last )
gt;
gt;I appreciate everyones help!!
gt;
gt;quot;Gord Dibbenquot; wrote:
gt;
gt;gt; Lady
gt;gt;
gt;gt; Maybe............
gt;gt;
gt;gt; Sub Indirect_Add()
gt;gt; Dim myStr As String
gt;gt; Dim cel As Range
gt;gt; For Each cel In Selection
gt;gt; If cel.HasFormula = True Then
gt;gt; If Not cel.Formula Like quot;=INDIRECT(*quot; Then
gt;gt; myStr = Right(cel.Formula, Len(cel.Formula) - 1)
gt;gt; cel.Value = quot;=INDIRECT(quot; amp; myStr amp; quot;)quot;
gt;gt; End If
gt;gt; End If
gt;gt; Next
gt;gt; End Sub
gt;gt;
gt;gt;
gt;gt; Gord Dibben Excel MVP
gt;gt;
gt;gt; On Tue, 13 Dec 2005 08:16:02 -0800, quot;ladyhawkquot;
gt;gt; gt; wrote:
gt;gt;
gt;gt; gt;Sorry for the confusion...first time using these boards....
gt;gt; gt;What I am trying to do is to update my absolute cells in about 8 columns and
gt;gt; gt;600 rows to include the instructions given to me in a reply to my earlier
gt;gt; gt;post...the reply said to change the cell formula to be:
gt;gt; gt;=INDIRECT(quot;'week1'!$A$4quot;)
gt;gt; gt; but I will need to do this for cells A1 thru L600
gt;gt; gt;I had read in a different post a quick macro to insert the $ making the
gt;gt; gt;cells absolute and was looking for a similar one to insert the new
gt;gt; gt;text(indirect(quot;).
gt;gt; gt;
gt;gt; gt;quot;JE McGimpseyquot; wrote:
gt;gt; gt;
gt;gt; gt;gt; What do you mean by quot;add this automatically for a group of cellsquot;? Would
gt;gt; gt;gt; all of the cells have the same formula? In that case just select the
gt;gt; gt;gt; cells and use CTRL-Enter. If not, how should the formulae be constructed?
gt;gt; gt;gt;
gt;gt; gt;gt; Note: It's definitely easier to figure out what you're referring to when
gt;gt; gt;gt; you add your follow-up to the reply, not the original question.
gt;gt; gt;gt;
gt;gt; gt;gt;
gt;gt; gt;gt; In article gt;,
gt;gt; gt;gt; quot;ladyhawkquot; gt; wrote:
gt;gt; gt;gt;
gt;gt; gt;gt; gt; Great...that definatly did the trick!!
gt;gt; gt;gt; gt; Now is there a macro to add this automatically for a group of cells?
gt;gt; gt;gt; gt;
gt;gt; gt;gt; gt; quot;ladyhawkquot; wrote:
gt;gt; gt;gt; gt;
gt;gt; gt;gt; gt; gt; I am trying to use an absolute cell refernce on tab 2 that refernces tab 1.
gt;gt; gt;gt; gt; gt; When I insert a row on tab 1 (tab name is week 1)the refernce changes. For
gt;gt; gt;gt; gt; gt; example cell A4 the formula is ='week1'!$a$4
gt;gt; gt;gt; gt; gt; when I insert a row on the week 1 tab it changes tab 2 formula for cell A4
gt;gt; gt;gt; gt; gt; to
gt;gt; gt;gt; gt; gt; ='week1'!$a$5
gt;gt; gt;gt; gt; gt;
gt;gt; gt;gt; gt; gt; What can I do to make it not make that change but still be able to insert
gt;gt; gt;gt; gt; gt; rows?
gt;gt; gt;gt; gt; gt;
gt;gt; gt;gt;
gt;gt;