Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Maintaining cell references when inserting cells elsewhere
I've got a problem that I can't seem to find mentioned elsewhere. I have
created a spreadsheet so that I can compare the contents of 2 separate databases - one an export of rows from an Access database, and the other selected cells from another spreadsheet, sorted using the same criteria. There are some differences between the 2 and I want to flag where the different values are. I have the "database" cells in columns A to K (about 9500 rows) and the "spreadsheet" cells in columns S to AD. I'll refer to these as the Left side and the Right side. In the middle, Columns L through R are used to compare and flag corresponding cells from the Access side with the Excel side of the file. For example, =IF(H2=X2,"","X") - will be blank if the 2 cells are equal and will show an "X" if they aren't. What happens is that I hit a record on one side that doesn't exist in the other - e.g. the Right side has a record that isn't in the Left, so from that point on they're out of sync. I need to add a "blank" bunch of cells on the Left side of the spreadsheet bump down the records below that point. When I do this, though, my formulae in L to R get altered also, so every time I have to add a row I need to recopy these formulae down to the bottom in order to update my "X" 's. Is there a way to "freeze" these formulae so they don't recalculate each time. I've dabbled with INDIRECT and OFFSET but can't get it to do what I want. Basically I just want those formulae to stay the same. Sorry to be so long winded. Thanks for any suggestions! -- Stephen |
#2
|
|||
|
|||
Hi Stephen,
you can try using the "$" sign in the formula so that the formula does not change also you can go to tools optionscalculation and you a many option for reclaculation including manual option. I think the 2nd option will definitely help. Also if all the data values are unique you can use the countif function also. do reply if this helped "Stephen Jefferson" wrote: I've got a problem that I can't seem to find mentioned elsewhere. I have created a spreadsheet so that I can compare the contents of 2 separate databases - one an export of rows from an Access database, and the other selected cells from another spreadsheet, sorted using the same criteria. There are some differences between the 2 and I want to flag where the different values are. I have the "database" cells in columns A to K (about 9500 rows) and the "spreadsheet" cells in columns S to AD. I'll refer to these as the Left side and the Right side. In the middle, Columns L through R are used to compare and flag corresponding cells from the Access side with the Excel side of the file. For example, =IF(H2=X2,"","X") - will be blank if the 2 cells are equal and will show an "X" if they aren't. What happens is that I hit a record on one side that doesn't exist in the other - e.g. the Right side has a record that isn't in the Left, so from that point on they're out of sync. I need to add a "blank" bunch of cells on the Left side of the spreadsheet bump down the records below that point. When I do this, though, my formulae in L to R get altered also, so every time I have to add a row I need to recopy these formulae down to the bottom in order to update my "X" 's. Is there a way to "freeze" these formulae so they don't recalculate each time. I've dabbled with INDIRECT and OFFSET but can't get it to do what I want. Basically I just want those formulae to stay the same. Sorry to be so long winded. Thanks for any suggestions! -- Stephen |
#3
|
|||
|
|||
Try this in L1, & copy it to all the comparison cells
=IF(OFFSET(L1,0,-11)=OFFSET(L1,0,11),"","X") "Stephen Jefferson" wrote: I've got a problem that I can't seem to find mentioned elsewhere. I have created a spreadsheet so that I can compare the contents of 2 separate databases - one an export of rows from an Access database, and the other selected cells from another spreadsheet, sorted using the same criteria. There are some differences between the 2 and I want to flag where the different values are. I have the "database" cells in columns A to K (about 9500 rows) and the "spreadsheet" cells in columns S to AD. I'll refer to these as the Left side and the Right side. In the middle, Columns L through R are used to compare and flag corresponding cells from the Access side with the Excel side of the file. For example, =IF(H2=X2,"","X") - will be blank if the 2 cells are equal and will show an "X" if they aren't. What happens is that I hit a record on one side that doesn't exist in the other - e.g. the Right side has a record that isn't in the Left, so from that point on they're out of sync. I need to add a "blank" bunch of cells on the Left side of the spreadsheet bump down the records below that point. When I do this, though, my formulae in L to R get altered also, so every time I have to add a row I need to recopy these formulae down to the bottom in order to update my "X" 's. Is there a way to "freeze" these formulae so they don't recalculate each time. I've dabbled with INDIRECT and OFFSET but can't get it to do what I want. Basically I just want those formulae to stay the same. Sorry to be so long winded. Thanks for any suggestions! -- Stephen |
#4
|
|||
|
|||
Works beautifully! Exactly what I wanted. Thanks very much!
-- Stephen "Duke Carey" wrote: Try this in L1, & copy it to all the comparison cells =IF(OFFSET(L1,0,-11)=OFFSET(L1,0,11),"","X") "Stephen Jefferson" wrote: I've got a problem that I can't seem to find mentioned elsewhere. I have created a spreadsheet so that I can compare the contents of 2 separate databases - one an export of rows from an Access database, and the other selected cells from another spreadsheet, sorted using the same criteria. There are some differences between the 2 and I want to flag where the different values are. I have the "database" cells in columns A to K (about 9500 rows) and the "spreadsheet" cells in columns S to AD. I'll refer to these as the Left side and the Right side. In the middle, Columns L through R are used to compare and flag corresponding cells from the Access side with the Excel side of the file. For example, =IF(H2=X2,"","X") - will be blank if the 2 cells are equal and will show an "X" if they aren't. What happens is that I hit a record on one side that doesn't exist in the other - e.g. the Right side has a record that isn't in the Left, so from that point on they're out of sync. I need to add a "blank" bunch of cells on the Left side of the spreadsheet bump down the records below that point. When I do this, though, my formulae in L to R get altered also, so every time I have to add a row I need to recopy these formulae down to the bottom in order to update my "X" 's. Is there a way to "freeze" these formulae so they don't recalculate each time. I've dabbled with INDIRECT and OFFSET but can't get it to do what I want. Basically I just want those formulae to stay the same. Sorry to be so long winded. Thanks for any suggestions! -- Stephen |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Maintaining cell reference after sorting | Excel Discussion (Misc queries) | |||
Fixed cell references | Excel Discussion (Misc queries) | |||
Displaying cell references next to embedded cells in Word 2000 | New Users to Excel | |||
Syntax for inferred cell references | Excel Worksheet Functions | |||
GET.CELL | Excel Worksheet Functions |