Need to compare 2 worksheets...
 

Hi there folks! Using E02 on XP. I'm looking for a bit of advice here,
wondering if I could be aimed in the right direction before I flounder about.

I have a set of files, in one (DataIn.xls) I paste a series spreadsheets
provided and the other (DataOut.xls) has shells (pretty presentation type,
printable data) with many, many cells linked to cells in DataIn.xls. My
output is good and chance of data entry error is next to nil.

My dilemma is this: there is an OLD file that requires all numbers be
manually keyed in from printed sheets (the same data I paste in my DataIn.xls
file). My boss has asked me to execute my linking procedure monthly for the
next 6 months AND provide something identifying all differences in the
numbers between MY DataOut.xls and the manually keyed sheet (that is set up
identically to my DataOut.xls). What is the best way to approach comparing
the 2 worksheets? I'm thinking I need to create a 3rd sheet/file that will
look at [DataOut.xls]Comp3'!$B$122 and compare it with
[Manual.xls]Comp3'!$B$122. If no difference, nothing, if a difference, show
"Error" in RED. Am I anywhere near base here?

Would appreciate any help or advice, need some direction. Thanks in advance
for your time!




--
Bonnie

Bonnie,

On your third sheet, in cell A1, use the formula

=IF('[DataIn.xls]Comp3'!A1='[Manual.xls]Comp3'!A1,"","Error")

and copy to as large a range as either of the other workbooks uses.

Then use conditional formatting to format Error as Red font or red background.

HTH,
Bernie
MS Excel MVP


"Bonnie" wrote in message
...
Hi there folks! Using E02 on XP. I'm looking for a bit of advice here,
wondering if I could be aimed in the right direction before I flounder about.

I have a set of files, in one (DataIn.xls) I paste a series spreadsheets
provided and the other (DataOut.xls) has shells (pretty presentation type,
printable data) with many, many cells linked to cells in DataIn.xls. My
output is good and chance of data entry error is next to nil.

My dilemma is this: there is an OLD file that requires all numbers be
manually keyed in from printed sheets (the same data I paste in my DataIn.xls
file). My boss has asked me to execute my linking procedure monthly for the
next 6 months AND provide something identifying all differences in the
numbers between MY DataOut.xls and the manually keyed sheet (that is set up
identically to my DataOut.xls). What is the best way to approach comparing
the 2 worksheets? I'm thinking I need to create a 3rd sheet/file that will
look at [DataOut.xls]Comp3'!$B$122 and compare it with
[Manual.xls]Comp3'!$B$122. If no difference, nothing, if a difference, show
"Error" in RED. Am I anywhere near base here?

Would appreciate any help or advice, need some direction. Thanks in advance
for your time!




--
Bonnie

Bernie,

Thank you SO much for the prompt, helpful reply. You have put me on the
path of less wasted effort and I thank you. I'll give it a go later today.

--
Bonnie


"Bernie Deitrick" wrote:

Bonnie,

On your third sheet, in cell A1, use the formula

=IF('[DataIn.xls]Comp3'!A1='[Manual.xls]Comp3'!A1,"","Error")

and copy to as large a range as either of the other workbooks uses.

Then use conditional formatting to format Error as Red font or red background.

HTH,
Bernie
MS Excel MVP


"Bonnie" wrote in message
...
Hi there folks! Using E02 on XP. I'm looking for a bit of advice here,
wondering if I could be aimed in the right direction before I flounder about.

I have a set of files, in one (DataIn.xls) I paste a series spreadsheets
provided and the other (DataOut.xls) has shells (pretty presentation type,
printable data) with many, many cells linked to cells in DataIn.xls. My
output is good and chance of data entry error is next to nil.

My dilemma is this: there is an OLD file that requires all numbers be
manually keyed in from printed sheets (the same data I paste in my DataIn.xls
file). My boss has asked me to execute my linking procedure monthly for the
next 6 months AND provide something identifying all differences in the
numbers between MY DataOut.xls and the manually keyed sheet (that is set up
identically to my DataOut.xls). What is the best way to approach comparing
the 2 worksheets? I'm thinking I need to create a 3rd sheet/file that will
look at [DataOut.xls]Comp3'!$B$122 and compare it with
[Manual.xls]Comp3'!$B$122. If no difference, nothing, if a difference, show
"Error" in RED. Am I anywhere near base here?

Would appreciate any help or advice, need some direction. Thanks in advance
for your time!




--
Bonnie

Need to compare 2 worksheets...
 

Hi Bernie

Searched this forum and found your help very useful

Thanks

Colin

"Bernie Deitrick" wrote:

Bonnie,

On your third sheet, in cell A1, use the formula

=IF('[DataIn.xls]Comp3'!A1='[Manual.xls]Comp3'!A1,"","Error")

and copy to as large a range as either of the other workbooks uses.

Then use conditional formatting to format Error as Red font or red background.

HTH,
Bernie
MS Excel MVP


"Bonnie" wrote in message
...
Hi there folks! Using E02 on XP. I'm looking for a bit of advice here,
wondering if I could be aimed in the right direction before I flounder about.

I have a set of files, in one (DataIn.xls) I paste a series spreadsheets
provided and the other (DataOut.xls) has shells (pretty presentation type,
printable data) with many, many cells linked to cells in DataIn.xls. My
output is good and chance of data entry error is next to nil.

My dilemma is this: there is an OLD file that requires all numbers be
manually keyed in from printed sheets (the same data I paste in my DataIn.xls
file). My boss has asked me to execute my linking procedure monthly for the
next 6 months AND provide something identifying all differences in the
numbers between MY DataOut.xls and the manually keyed sheet (that is set up
identically to my DataOut.xls). What is the best way to approach comparing
the 2 worksheets? I'm thinking I need to create a 3rd sheet/file that will
look at [DataOut.xls]Comp3'!$B$122 and compare it with
[Manual.xls]Comp3'!$B$122. If no difference, nothing, if a difference, show
"Error" in RED. Am I anywhere near base here?

Would appreciate any help or advice, need some direction. Thanks in advance
for your time!




--
Bonnie