changing the formula's row reference
 

In column A, I have a number of formulae that refer to other cells on the
worksheet. For example, in A1, the formula is = BB30, A2=BD30, A3=BG30, etc.
In these formulae, the columns are all different, but the row number is
always the same.

However, I need to update the formulae to reflect a new row, so that
A1=BB31, A2=BD31, A3=BG31. The old column references will stay the same, and
the row references will all be the same number. Is there a way to do this
quickly and easily for a large number of formulae?

Thanks!

You could do a search and replace, Ctrl-H.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"redb" wrote in message
...
In column A, I have a number of formulae that refer to other cells on the
worksheet. For example, in A1, the formula is = BB30, A2=BD30, A3=BG30,
etc.
In these formulae, the columns are all different, but the row number is
always the same.

However, I need to update the formulae to reflect a new row, so that
A1=BB31, A2=BD31, A3=BG31. The old column references will stay the same,
and
the row references will all be the same number. Is there a way to do this
quickly and easily for a large number of formulae?

Thanks!

this should do it but I suggest defining a name for your ranges and using
that.
Sub changeformula()
For Each c In Selection
x = c.Formula
c.Formula = Left(x, Len(x) - 2) + "33"
Next
End Sub

--
Don Guillett
SalesAid Software

"redb" wrote in message
...
In column A, I have a number of formulae that refer to other cells on the
worksheet. For example, in A1, the formula is = BB30, A2=BD30, A3=BG30,
etc.
In these formulae, the columns are all different, but the row number is
always the same.

However, I need to update the formulae to reflect a new row, so that
A1=BB31, A2=BD31, A3=BG31. The old column references will stay the same,
and
the row references will all be the same number. Is there a way to do this
quickly and easily for a large number of formulae?

Thanks!

Hello redb-

Select the cells containing your "=BB30" formulae. Go to the Edit Bar and
change the row ref to =BB35 and hold the Ctrl key while you press Enter.

HTH |:)

"redb" wrote:

In column A, I have a number of formulae that refer to other cells on the
worksheet. For example, in A1, the formula is = BB30, A2=BD30, A3=BG30, etc.
In these formulae, the columns are all different, but the row number is
always the same.

However, I need to update the formulae to reflect a new row, so that
A1=BB31, A2=BD31, A3=BG31. The old column references will stay the same, and
the row references will all be the same number. Is there a way to do this
quickly and easily for a large number of formulae?

Thanks!

you can also use this formula to auto adjust where you are looking for a
number larger than possible. Use "zzzzz" for text

=INDIRECT("bb"&MATCH(9999999,bb:bb))

--
Don Guillett
SalesAid Software

"redb" wrote in message
...
In column A, I have a number of formulae that refer to other cells on the
worksheet. For example, in A1, the formula is = BB30, A2=BD30, A3=BG30,
etc.
In these formulae, the columns are all different, but the row number is
always the same.

However, I need to update the formulae to reflect a new row, so that
A1=BB31, A2=BD31, A3=BG31. The old column references will stay the same,
and
the row references will all be the same number. Is there a way to do this
quickly and easily for a large number of formulae?

Thanks!

Hi redb-

Apology for my earlier reply. Misread your post, and although what I offered
would work if your formulae & data in rows _or_ columns, it won't if the
formulae are in a column & your data is in a row. Sorry for the mis-info.

Bob's suggestion is a much better and more effective solution if that is how
your data is set up. As per your example, select the cells in column A,
'Find' for 30 & 'Replace' with 31.

Regards |:)

"redb" wrote:

In column A, I have a number of formulae that refer to other cells on the
worksheet. For example, in A1, the formula is = BB30, A2=BD30, A3=BG30, etc.
In these formulae, the columns are all different, but the row number is
always the same.

However, I need to update the formulae to reflect a new row, so that
A1=BB31, A2=BD31, A3=BG31. The old column references will stay the same, and
the row references will all be the same number. Is there a way to do this
quickly and easily for a large number of formulae?

Thanks!