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

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

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

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