D
Danny@Kendal
Is there a way to find and replace old formulas with new formulas but
keeping certain relative addressing parameters?
Or is there a way to enter truly relative cell references rather than the
moveable absolute references which are often called 'relative'?
(eg: "2 cells left of this one" rather than "B3")
The old formula is as below (not mine)
=IF(LOOKUP(B3,ACC!$A$2:$A$16013,ACC!$A$2:$A$16013)=B3,LOOKUP(B3,ACC!$A$2:$A$
16013,ACC!$B$2:$B$16013),"Account Code Error (or created after 5/11/03)")
The one I want to replace it with is this one (mine!)
=VLOOKUP(B3,Accounts,2,FALSE)
'Accounts' refers to a master list of account and address details.
It puts the account name into each cell of column D (which is where the
formulas reside) depending on the account number in the corresponding cell
in column B. Sometimes a generic account is used then the data for the cell
in column D is manually entered.
These non-formula cells prevent me from updating the formula at the
beginning of the sheet and using fill-down.
Using GOTO - FORMULAS and then filling down doesn't work either because it
stops filling at the first non-formula cell.
I got round it by sorting by account order, performing two fill-downs
avoiding the generic account rows then resorting back to date order.
Not an elegant solution but it works.
keeping certain relative addressing parameters?
Or is there a way to enter truly relative cell references rather than the
moveable absolute references which are often called 'relative'?
(eg: "2 cells left of this one" rather than "B3")
The old formula is as below (not mine)
=IF(LOOKUP(B3,ACC!$A$2:$A$16013,ACC!$A$2:$A$16013)=B3,LOOKUP(B3,ACC!$A$2:$A$
16013,ACC!$B$2:$B$16013),"Account Code Error (or created after 5/11/03)")
The one I want to replace it with is this one (mine!)
=VLOOKUP(B3,Accounts,2,FALSE)
'Accounts' refers to a master list of account and address details.
It puts the account name into each cell of column D (which is where the
formulas reside) depending on the account number in the corresponding cell
in column B. Sometimes a generic account is used then the data for the cell
in column D is manually entered.
These non-formula cells prevent me from updating the formula at the
beginning of the sheet and using fill-down.
Using GOTO - FORMULAS and then filling down doesn't work either because it
stops filling at the first non-formula cell.
I got round it by sorting by account order, performing two fill-downs
avoiding the generic account rows then resorting back to date order.
Not an elegant solution but it works.