Search worksheets for values missing from summary sheet [SOLVED]

[sshea]

This might be an impossible task:


I have a workbook with ~50 worksheets and then a summary worksheet. Each worksheet has a list of codes in column C and a dollar amount associated with it in column E. My summary sheet has codes listed in column C and then the combined total for that code from any of the 50 sheets that contain that code in column E. However, not every worksheet has the same list of codes - my summary page right now has ~200 codes and each worksheet has ~50-100 various codes. Is there a formula or process I can use to highlight/find out what codes occur in the worksheets that I am missing from my list on my summary sheet?

 

[SirJB7]

Hi, sshea!


You can try using conditional formatting on cells of each column C used range in detail worksheets with a formula like this:

=ESNOD(BUSCARV(C2;'Summary sheet'!C:C;1;FALSO)) -----> in english: =ISNA(VLOOKUP(C2,'Summary sheet'!C:C,1,FALSE))

where 'Summary sheet' is the name of the summary worksheet (embracing apostrophes may be ommited if name doesn't contain spaces).


Regards!

 

[sshea]

Thank you for your help, however, it's highlighting values that already exist on the summary sheet and, as a constant, I added a value on a worksheet that i know does not exist on the summary sheet and it is not highlighting.

 

[SirJB7]

Hi, sshea!

Consider uploading a sample file (including manual examples of desired output if applicable), it'd be very useful for those who read this and might be able to help you. Thank you. Give a look at the green sticky posts at this forums main page for uploading guidelines.

Regards!

 

[sshea]

Thanks!


https://www.dropbox.com/s/3ww1teuvftzmrt9/Book1.xlsx


In this example the summary page is missing code MNO. MNO is on sheet3 but not SUMMARY. With having a workbook w/ 50 sheets of ~100 rows of various codes each, and a SUMMARY page of 200+ rows of codes, I need a way to be aware of a code existing in a worksheet and not on SUMMARY sheet. My SUMMARY sheet has the majority of codes listed but I know I've missed some and in the future some new ones might come up on a sheet# and need to be added to SUMMARY.


All your help is appreciated! THanks!!

 

[SirJB7]

Hi, sshea!


Give a look at this file:

https://dl.dropboxusercontent.com/u/60558749/Search%20worksheets%20for%20values%20missing%20from%20summary%20sheet%20-%20Book1%20%28for%20sshea%20at%20chandoo.org%29.xlsx


As I wrote in my first post the CF formula to be applied to ranges with data en column C of each worksheet (different from summary) is this:

=ESNOD(BUSCARV(C2;SUMMARY!$C$2:$C$15;1;FALSO)) -----> in english: =ISNA(VLOOKUP(C2,SUMMARY!$C$2:$C$15,1,FALSE))

applied to the range C2:C12 in the case of worksheet Sheet3, and C2:C10 for Sheet2.


Regards!

 

[sshea]

Works great! Thanks so very much!