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 >options>calculation 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