List Physical Cell Address based on Non-Match to Specific Data

Hi Everyone!

I have a set of Dashboards that (in hindsight) need to include a method to error check the data entered.

There are 13 sheets of consequence named: Dashboard, Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sept, Oct, Nov, Dec.

On the "Dashboard" sheet, aside from pivot charts, there is a table of codes that staff must use to code their daily activities (these columns serve mainly as a reference for processing the pivot tables on another sheet). The "Month" sheets are where the data is entered for each workday.

The Dashboard Code table runs from AW7:BB120. Columns AW (Municipality), AY (Program), and BA (Activity) contain the codes, and other three columns explain the codes (e.g., Code: BRK Name: Break).

On all of the "Month" Sheets, these codes are entered in columns L through Q. Each row of these columns contains a merged cell (i.e. L10:M10, N10:O10, P10:Q10). The cells in the merged "L:M" column are where the Municipality codes are entered ("Dashboard" Column AW), "N:O" is where the Program codes are entered ("Dashboard" Column AY), and "P:Q" are where the Activity codes are entered ("Dashboard Column BA").

The full range for data entry is L10:Q1600.

What I'm hoping to achieve: I'd like to be able to run a macro on whichever Month is the activesheet that compares the data columns on that month's sheet to the data table on the Dashboard sheet. I'd like it to then list the physical address of any cell that has data entered but does not match one of the Dashboard table's codes (e.g., if cell N249 had the code BK instead of BRK (for break).

There are only two duplicate codes that exist in both the Program and Activity Columns. They are BRK for Break and UNL for Unpaid Leave. Otherwise all codes are unique.

Is this possible to achieve with a macro?

Ideally the code would write the physical cell addresses to the merged cell V5:AF7 on sheet it is run on, separating each address with a comma.

Any and all help would be greatly appreciated!!!!

Thank you in advance,

-LM

Hi CK,

Thanks for your initial reply. I am uploading a truncated version of the Dashboard to fit the posting size limit(only the main dashboard page and the Month of January). I left the codes and the actual data this employee used but changed the program/activity/township names (only has bearing on what shows in the pivot charts/slicers).

I also created the section on January that I would like (if possible) the output to look like. All macro's in this sheet are safe, as they either came form myself or other persons on this forum.

There are actual errors in this sheet, so if the macro works, it should find them (I didn't try to track them down myself, but the errors appear to be related to the activities for January).

Thank you!

Hi All!

Has anyone had any luck in reviewing my problem? (Sorry for bumping)

Thank you!

I looked at your file briefly, but you don't have any data for me to check in Jan sheet.

Can you give few examples (5 rows should be enough, mix of correct input and wrong input)?

Hi CK,

Sorry for not describing the sheet better. For every day of every month there are 50 spaces for potential data entry (most are blank for a typical user). That's why the numbers at the top (corresponding to a day of the month) were red or green (red for no data, green for data).
I have added data to Day 1 and intentionally made errors in cells N10, P11, N16, L19, P20, and N27. So any potential macro should at minimum identify those cell addresses as not matching the codes in the table on the Dashboard Sheet (range AW7:BB120). There are also a few legitimate errors on some of the other days (but I don't know where myself as I didn't track them down).

The full range on each month the macro would need to check for errors is L10:Q1600.

I don't need to know what the errors are, just where they are located so a clerk can double check them against the standard list.

If this isn't clear or you have questions about another aspect of the sheet, please let me know.

Thank you so much for your assistance!

I'm having bit of trouble identifying what you consider as invalid/valid.

This is my take on it at the moment.
If Program Code is found in AY column of Dashboard, it is ok. Else error.
For Activity Code, in BA column.
For TWP Code it must be found in AW column?

Let me know if this is correct.

Had bit of time so I went ahead and coded using above assumption. Test it and let me know if this does what you are looking for.

Note: Code must be run, while sheet that's being checked is active (should not be issue as you are assigning it to button).

Please Login or Register  to view this content.

CK!!!

It works perfectly!!! Thank you so very much!! If I discover any unexpected operations, I'll let you know, but it appears to be executing perfectly. Thank you thank you!

Hi CK & All!

Is it possible to amend this code? I've discovered it will only show an error in the program and activity columns if the township column has an entry (i.e., if township doesn't have an entry and the other two do, it won't display the township cell as being in error).

Upon updating the dashboards with CK's code, I've also found a few entries where the individual only put their daily time with no codes in the three columns.

So here's what I'm wondering: Can the code be amended such that if any data is entered in the time column (merged cells "J:K"), the township column ("L:M"), the program column ("N:O") and/or the activity column ("P:Q"), the cells with missing data will also be recognized as if they are incorrect code?

CK's current code does this partially (i.e., if the program or activity were missing, it calls up those cell addresses so long as the township code is present). But nothing at the moment will call up a missing township code, or in the case I've just found, indicate a problem where the individual only listed their time on a particular day and had no associated codes.

If you have the time to look at this, thank you!!

Ok, so you want to check for 3 merged ranges, whenever there is Time value.

Let's go step by step.
First you need to change the range used to fill chkArr.
Since time is stored in J:K merged column...

Please Login or Register  to view this content.

Next, you need to adjust each column index (array) used to check against twpRng, prgRng, actRng.

If statement does not change since you are still checking if first column contains value (Time).

Please Login or Register  to view this content.

for twpRng...

Please Login or Register  to view this content.

for prgRng...

Please Login or Register  to view this content.

Basically, each check's column index is increased by 2 (since J & K are added to column range).

So code becomes...

Please Login or Register  to view this content.

Cheers CK, you rock!

This should work as I checked with the clerk who enters the data and time should always be present (even if 0 because the activity was much shorter than 15 minutes).

Thank you again for all of your help and your amazingly fast reply to my second request!