Home |
Search |
Today's Posts |
|
#1
|
|||
|
|||
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! |
#2
|
|||
|
|||
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! |
#3
|
|||
|
|||
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! |
#4
|
|||
|
|||
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! |
#5
|
|||
|
|||
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! |
#6
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sort data without formulas in other cells changing? | Excel Discussion (Misc queries) | |||
changing multiple cells from relative to absolute reference | Excel Discussion (Misc queries) | |||
A cell reference in a formula changing | Excel Worksheet Functions | |||
Relative worksheet reference in 3-D formulas? | Excel Worksheet Functions | |||
Changing Cell References in Formulas | Excel Worksheet Functions |