Pivot Table (behind the scene)

[DHN]

Hello MVPs,

I need help with a formula that would be able to do the same as a filter in
a pivot table. I have two filters that I would like to be able to do. The
reason why I can not use a pivot table is because I have data on multiple
tabs within the workbook.

If I am not clear with the issue please let me know. Thanks.

Dai

 

[Roger Govier]

Hi Dai

Not enough information.
What does your data look like?
What are you trying to achieve?

--
Regards
Roger Govier

Hello MVPs,

I need help with a formula that would be able to do the same as a filter
in a pivot table. I have two filters that I would like to be able to do.
The reason why I can not use a pivot table is because I have data on
multiple tabs within the workbook.

If I am not clear with the issue please let me know. Thanks.

Dai


__________ Information from ESET Smart Security, version of virus
signature database 4741 (20100104) __________

The message was checked by ESET Smart Security.

http://www.eset.com

__________ Information from ESET Smart Security, version of virus signature database 4741 (20100104) __________

The message was checked by ESET Smart Security.

http://www.eset.com

 

[DHN]

Hi Roger,

If you could help me with one formula I can mimic the othe formula.

Filter 1 = VP1 (say in cell A1 of the blank worksheet) will be setup as Data
Validation List
Filter 2 = Date (say in cell A2 of the blank worksheet) will be setup as
Data Validation List

On a blank worksheet1 (a tab within the workbook where the data is) if I
select VP1 and Date (say 12/31/2009) then it would "lookup" and populate in
column A (starting at row 5) a list of "Project ID" associated with VP1
(from worksheet2). It would also populate in column B dollar amount for the
"Project ID" associated with VP1 for 12/31/2009 (from worksheet2). It would
also polulate in column C dollar amount for the "Project ID" associated with
VP1 for 12/31/2009 (from worksheet3).

Unfortunately I can not combine worksheet2 and worksheet3. If I could
combine I would just use Pivot Table to get the same result.

Naturally I would have to put formula down column A, B, and C so that when I
select Filter 1 and Filter 2 it would populate the data in column A, B, and
C.

Is this detail enough?

Dai

 

[Roger Govier]

Hi Dai

What you need is Advanced Filter.
Rather than me trying to describe the process, there is an excellent
example, along with some downloadable files on Debra Dalgleish's site (and a
video)
http://www.contextures.com/xladvfilter01.html#ExtractWs

Take a look there, and post back if you get stuck.

--
Regards
Roger Govier

Hi Roger,

If you could help me with one formula I can mimic the othe formula.

Filter 1 = VP1 (say in cell A1 of the blank worksheet) will be setup as
Data Validation List
Filter 2 = Date (say in cell A2 of the blank worksheet) will be setup as
Data Validation List

On a blank worksheet1 (a tab within the workbook where the data is) if I
select VP1 and Date (say 12/31/2009) then it would "lookup" and populate
in column A (starting at row 5) a list of "Project ID" associated with VP1
(from worksheet2). It would also populate in column B dollar amount for
the "Project ID" associated with VP1 for 12/31/2009 (from worksheet2). It
would also polulate in column C dollar amount for the "Project ID"
associated with VP1 for 12/31/2009 (from worksheet3).

Unfortunately I can not combine worksheet2 and worksheet3. If I could
combine I would just use Pivot Table to get the same result.

Naturally I would have to put formula down column A, B, and C so that when
I select Filter 1 and Filter 2 it would populate the data in column A, B,
and C.

Is this detail enough?

Dai








__________ Information from ESET Smart Security, version of virus
signature database 4743 (20100104) __________

The message was checked by ESET Smart Security.

http://www.eset.com

__________ Information from ESET Smart Security, version of virus signature database 4743 (20100104) __________

The message was checked by ESET Smart Security.

http://www.eset.com

 

[DHN]

Roger,

Great feature from Debra's site. This would be great if when I change the
filter criteria and that it would automatically update the filtered data.

Any other suggestions?

Dai

 

[Roger Govier]

Hi Dai

look at other examples where Debra shows how to use a macro to update the
results after you change the criteria

--
Regards
Roger Govier

Roger,

Great feature from Debra's site. This would be great if when I change the
filter criteria and that it would automatically update the filtered data.

Any other suggestions?

Dai





__________ Information from ESET Smart Security, version of virus
signature database 4743 (20100104) __________

The message was checked by ESET Smart Security.

http://www.eset.com

__________ Information from ESET Smart Security, version of virus signature database 4743 (20100104) __________

The message was checked by ESET Smart Security.

http://www.eset.com

 

[Herbert Seidenberg]

Excel 2007 PivotTable
Consolidate diverse Tables on multiple tabs
with macro, then Pivot:
http://c0444202.cdn.cloudfiles.rackspacecloud.com/01_04_10.xlsm

 

[DHN]

Herbert et al.

Thank you for the help thus far.

These are great tools and I can use them on some other things that I do.
But for what I would like to do is this. I have three tabs (Summary,
Forecast, Actual) as depict below. On the summary tab is where the
individual change the filter and the Project ID, Forecast, Actual data would
populate on the summary tab by pulling the data on the forecast and actual
tab.

On the summary tab I will have a calculated field call "Variance" which is a
simple budget minus actual. This is not showing here just for simplicity
sake. I was hoping that there is a formula using INDEX, MATCH, OFFSET, etc.
for the data to pull into the summary tab.

My goal is to "dummy proof" this template/model so that anyone can change
the filter fields and have their summary data for their respective group.
All fields will be locked except for the filter fields.

Does this make sense?

Thanks for all the help. I am using Office 2007.

Dai


SUMMARY tab:

Group: DOC <== Filter field
Date: 12/31/2009 <== Filter field

Project ID Forecast Actual

1001 5000 4850
1011 6000 6810
1021 7000 4685

FORECAST tab:

Project ID Group 10/31/2009 11/30/2009 12/31/2009

1001 DOC
5000
1002 GES
100
1003 COM 10000
1011 DOC
6000
1012 GES
200
1013 COM 15000
1021 DOC
7000
1022 GES
300
1023 COM
20000

ACTUAL tab:

Project ID Group 10/31/2009 11/30/2009 12/31/2009

1001 DOC
4850
1002 GES
2195
1003 COM
8965
1011 DOC
6810
1012 GES
598
1013 COM
11456
1021 DOC
4685
1022 GES
493
1023 COM
13264

 

[Herbert Seidenberg]

Excel 2007 PivotTable, PivotChart
Consolidate Tables with PT, type "Consolidate".
Reverse PT with macro, then Pivot as usual.
LCD user, security not addressed.
Applied PT, not formulas, for max flexibility, looks.
http://c0444202.cdn.cloudfiles.rackspacecloud.com/01_07_10.xlsm