Or rather more quickly and less transatlantically...
Just hold down the control key while clicking on the left and right arrows that are there (as the help prompt will display if you hover over the arrows).
If the information is in separate workbooks then, providing it is entered consistently in all the workbooks, the Excel Add-in (now part of Excel 2016 as Get and Transform) can look through a folder and read in all the filenames, use the filenames to open each file, read in all the sheets from Excel files and then read in all the rows from all the sheets to create one big table. Once you've got everything into a single table then you could create your dashboard using PivotTables and PivotCharts.
I wouldn't have called the 'True' version of VLOOKUP() fuzzy, it's actually very precise, choosing the largest item that's less than or equal to the lookup value, hence the need for sorting. If you do use the approximate version with an unsorted first column the results can be close to random.
I wouldn't disagree with most of what SHCTax added which is why I suggested Power Query as an alternaive rather than a preferred solution. However, I would argue about the 'error prone' issue. The Power Query is not without risks, particularly as the need to refresh queries requires additional discipline compared to automatic formula recalculation. On the other hand, a query based solution has the potential to be a lot less error prone then a 'traditional' cell-based approach. Rather than possibly thousands of individual formula cells, each of which could have an error, or could be accidentally overwritten, using a query involves 3 or 4 procedural steps and no need for the user to construct any formula at all.
I think it would be a shame if Excel users just assumed that database techniques were more difficult than cell-based formulae and thereby missed out on the opportunity to make creating and using spreadsheets quicker, easier and safer.
If you only need to highlight the non-matches then many of the above suggestions are useful, although I'm not sure they all distinguish between which list the numbers are missing from. A different approach is possible using Power Query (or if you have Excel 2016 Get & Transform). Load List A and List B as separate queries. Merge the two queries with List B as the first query matching to List A and not choosing only to show matching rows. Expand List A and select only 'nulls'. If I've got the logic round the right way, that should show all the B items where there is no match in A. Rather than just mark the non-matches, this will create a separate list of non-matches that can be refreshed to reflect changes in either list.
Thanks Clint - I thought it might be that. I think I understand exactly what you are saying but, in my experience, the issue is that going to Custom and choosing one of the existing formats doesn't (strangely!) mean you are using a Custom number format. The standard number, accounting and currency formats are included in the Custom list. The following is what I think happens and I would be grateful for confirmation or even contradiction:
If you choose one of the 'standard' formats displayed in the Custom list the format used is Number, Accounting or Currency. Having applied the format, if you check the Number type displayed it will not be Custom but one of the three formats mentioned. As such, exactly as you say, it will be overridden by the computer's regional settings. However, setting up a Custom format that is not already in the list (such as adding the double-quotes, but also just by adding the zero formatting of -? or whatever) creates an actual Custom format that shows up in the Number type box as Custom. I have not tested this exhaustively but I think if a workbook includes a 'real' Custom format, then this is not overridden by the Regional Settings.
Also, as author of some of the articles referred to and, having chosen "Negative number formatting for accountants in Microsoft Excel 2007, Service Pack 2" as my Mastermind specialist subject, I am intrigued by Clint Westwood's comment:
... despite all those resources there is at least one point made in this thread hitherto lacking.
Following on from one of Paul Wakefield's points, if the file contains VBA code and was originally saved with a .XLSM extension, renaming to to .XLSX in the file system could cause this issue (intentionally, as Excel seeks to ensure that all files containing VBA code are seen as distinct from those that don't due to the malware implications)
Also, if you exit Excel and try and re-open the same file does it still happen? Sometimes Excel runs out of resources the more files you have open and this can cause strange error messages until you exit and re-open.
Yes, Edit Links is probably safer than find and replace. You could also create a macro based on the use of Edit Links with the date part of the file name coming from a cell entry if all else fails.
My answers
Or rather more quickly and less transatlantically...
Just hold down the control key while clicking on the left and right arrows that are there (as the help prompt will display if you hover over the arrows).
Power Query/Get and Transform
If the information is in separate workbooks then, providing it is entered consistently in all the workbooks, the Excel Add-in (now part of Excel 2016 as Get and Transform) can look through a folder and read in all the filenames, use the filenames to open each file, read in all the sheets from Excel files and then read in all the rows from all the sheets to create one big table. Once you've got everything into a single table then you could create your dashboard using PivotTables and PivotCharts.
Fuzzy VLOOKUP()?
I wouldn't have called the 'True' version of VLOOKUP() fuzzy, it's actually very precise, choosing the largest item that's less than or equal to the lookup value, hence the need for sorting. If you do use the approximate version with an unsorted first column the results can be close to random.
and leaping to Power Query's defence...
I wouldn't disagree with most of what SHCTax added which is why I suggested Power Query as an alternaive rather than a preferred solution. However, I would argue about the 'error prone' issue. The Power Query is not without risks, particularly as the need to refresh queries requires additional discipline compared to automatic formula recalculation. On the other hand, a query based solution has the potential to be a lot less error prone then a 'traditional' cell-based approach. Rather than possibly thousands of individual formula cells, each of which could have an error, or could be accidentally overwritten, using a query involves 3 or 4 procedural steps and no need for the user to construct any formula at all.
I think it would be a shame if Excel users just assumed that database techniques were more difficult than cell-based formulae and thereby missed out on the opportunity to make creating and using spreadsheets quicker, easier and safer.
Power Query/ Get & Transform
If you only need to highlight the non-matches then many of the above suggestions are useful, although I'm not sure they all distinguish between which list the numbers are missing from. A different approach is possible using Power Query (or if you have Excel 2016 Get & Transform). Load List A and List B as separate queries. Merge the two queries with List B as the first query matching to List A and not choosing only to show matching rows. Expand List A and select only 'nulls'. If I've got the logic round the right way, that should show all the B items where there is no match in A. Rather than just mark the non-matches, this will create a separate list of non-matches that can be refreshed to reflect changes in either list.
If it's a PivotTable based on an OLAP source, you could try using the Fields, Items and Sets option to create a set that omits the last column.
Custom or not
Thanks Clint - I thought it might be that. I think I understand exactly what you are saying but, in my experience, the issue is that going to Custom and choosing one of the existing formats doesn't (strangely!) mean you are using a Custom number format. The standard number, accounting and currency formats are included in the Custom list. The following is what I think happens and I would be grateful for confirmation or even contradiction:
If you choose one of the 'standard' formats displayed in the Custom list the format used is Number, Accounting or Currency. Having applied the format, if you check the Number type displayed it will not be Custom but one of the three formats mentioned. As such, exactly as you say, it will be overridden by the computer's regional settings. However, setting up a Custom format that is not already in the list (such as adding the double-quotes, but also just by adding the zero formatting of -? or whatever) creates an actual Custom format that shows up in the Number type box as Custom. I have not tested this exhaustively but I think if a workbook includes a 'real' Custom format, then this is not overridden by the Regional Settings.
Regional settings and missing point
One of the articles John Stokdyk mentioned goes through the regional settings process in some detail:
ExcelZone Compendium: Formatting tips (Excel 2007 update)
Also, as author of some of the articles referred to and, having chosen "Negative number formatting for accountants in Microsoft Excel 2007, Service Pack 2" as my Mastermind specialist subject, I am intrigued by Clint Westwood's comment:
... despite all those resources there is at least one point made in this thread hitherto lacking.
Which point are you referring to?
Simon Hurst
Or a file with a macro
Following on from one of Paul Wakefield's points, if the file contains VBA code and was originally saved with a .XLSM extension, renaming to to .XLSX in the file system could cause this issue (intentionally, as Excel seeks to ensure that all files containing VBA code are seen as distinct from those that don't due to the malware implications)
Also, if you exit Excel and try and re-open the same file does it still happen? Sometimes Excel runs out of resources the more files you have open and this can cause strange error messages until you exit and re-open.
Good point
Yes, Edit Links is probably safer than find and replace. You could also create a macro based on the use of Edit Links with the date part of the file name coming from a cell entry if all else fails.