Nekkidbuns
12-08-2008, 02:42 AM
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
MainThe 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
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
MainThe 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