Perhaps, You could import each spreadsheet using Import Records into different tables of the same file and then use a variety of methods to check for discrepancies.
But there are many different types of discrepancies from the total number of rows of data in the spreadsheet to specific text in a specific cell of that spreadsheet so much depends and the specific discrepancies that you need to check for.
And you might consider replacing your spreadsheets with a hosted database designed to prevent the discrepancies from occurring in the first place.
Thank you for the fast response. Unfortunately, a hosted DB as you suggested is unlikely to happen. But you've given me the first step in the mechanics of how a solution might be developed ie different tables. Then all that would be needed is probably a standardized set of queries to identify discrepancies and hopefully generate a report. I'm guessing that this might not be terribly difficult (even for me). There would be a limited set of fields that would be tested against each other. A typical issue among these spreadsheets might be an empty field (container) in one table as opposed to some information in a different spreadsheet. Or dates not agreeing with each other.
FileMaker container fields are used to store images or other files and are the one type of data that you can't import from a spreadsheet due to design differences in the two systems.
By using relationships, you can literally list the rows of spreadsheet data in paired rows where the data from spreadsheet 1 is in the upper row of the pair and the corresponding row of data from spreadsheet two can be placed in the lower row of the pair. Conditional formatting can then highlight fields where the data in a field of row 1 does not match the data in a corresponding field of row 2. You could also set up calculation fields that return a value when a pair of fields are not equal and you could then perform a find for that value in that calculation field to find a discrepancy.
But the time spent setting up the DB, the time spent periodically looking for and correcting the discrepancies thus found would seem to me to quickly equal or exceed the cost of getting several copies of FileMaker and setting up a hosted peer to peer database for the same tasks, but with safeguards to prevent the discrepancies from taking place. (I'm not really trying to convince you of this, but to provide you with a rationale to use with those that would need to agree to this just on the off chance it provides you with the needed ammunition to effect a useful change in your organization.)
Excellent Phil. That is very helpful. BTW - I'll be happy NOT to have to deal with container fields should I go forward with this. They're complicated from the perspective of this newbie/untrained developer.
Re a peer to peer - I think I would probably need to show proof of concept in my more basic approach.
Our agency has a site license encompassing (I believe) the entire suite of Fmp products (except maybe Go) so there would not be any acquisition cost to needing multiple copies of fm or having several people with Fmp access.
Just having something like you describe where one could visually examine the status of related fields would be useful to generate a summary report to the limited group of people that need to get it.
I think I would probably need to show proof of concept in my more basic approach.
But the database you create here to find and resolve discrepancies would not be a database that you would host and use to prevent them from occurring in the first place. It might open a few eyes at to what is possible with FileMaker but it wouldn't be a working example of what I am suggesting to use as a long term solution.
And FM GO, BTW, is a free download from the App store so anyone with an iOS device can get it free of charge.