Compare values in dedicated lists to values in pivottables.

[Nekkidbuns]

Hello everybody,

I'm kind of new to vba and don't really know what all the terms are, so please forgive me if this has been explained elsewhere in the forums. I simply couldn't find it.

This is what I've got. 5 tabs:

• Raw data
• Pivot APX
• Pivot ATS
• Pivot MAX
• Main

The Pivot tables get their data from the "Raw data" tab. The "main" page shows the data from the pivot tables in three dedicated lists (list per pivot) with additional calculations under each of them.

There are names in the second row of the list (column D of the worksheet).
The rest of the columns in the lists contain vLookups that refer to these names and looks them up in the pivottables (i have have created a formula that lists all of the names that are in the pivottables in the "A" column of each tab (with =IF((OR(D9="(blank)";D9="Grand Total";D9=""));"";D9) ))

What I need to have Excel do:

• Look up the names in de dedicated lists (column D) and compare them to the names in column A of the Pivot APX, ATS and MAX tabs.
• If there is a name missing on the "main" page, add this name to the list.
• If there is a name on the "main" page, but not in the pivot, do nothing and leave the name in.

This would be simple if there is only one list on the "main" page, but there are three located under each other. Sometimes a name shows up in each of the lists.
Also, the range of names Excel needs to look in changes when more names are added.
Basicly, the ranges on the "main" tab I want excel to look in are between these values in column D: "APX NAMES" to "APX TOTAL", "ATS NAMES" to "ATS TOTAL" and "MAX NAMES" to "MAX TOTAL".

I hope I explained it clearly enough. Any help would be greatly appreciated! Also, if you think this is not very efficient and you know of a better way, please tell me...im fresh to this!

Thanks in advance,


Roger

[Bob Phillips]

Post your workbook for clarification.

[Nekkidbuns]

Of course,
Here you go.

(this is ofcourse a ... discrete version )

[Nekkidbuns]

Alright, I finally figured out how to define the named ranges I wanted. I still need a way to compare them though. I have a range on the main tab "myMAINrange" and a range on the pivot APX tab "myAPXrange". Both of these ranges contain a list of names.

The pivot tables will change every week. Some names will be there again others might not (if the people dont make any hours).
I want Excel to look in myAPXrange en check in myMAINrange if there are names missing.
If it does, paste the missing names into the list of myMAINrange.

Anyone?