If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Pivot Table (behind the scene)
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 |
#2
|
|||
|
|||
Pivot Table (behind the scene)
Hi Dai
Not enough information. What does your data look like? What are you trying to achieve? -- Regards Roger Govier "DHN" wrote in message ... 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 |
#3
|
|||
|
|||
Pivot Table (behind the scene)
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" roger@technology4unospamdotcodotuk wrote in message news Hi Dai Not enough information. What does your data look like? What are you trying to achieve? -- Regards Roger Govier "DHN" wrote in message ... 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 |
#4
|
|||
|
|||
Pivot Table (behind the scene)
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 "DHN" wrote in message ... 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" roger@technology4unospamdotcodotuk wrote in message news Hi Dai Not enough information. What does your data look like? What are you trying to achieve? -- Regards Roger Govier "DHN" wrote in message ... 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 __________ 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 |
#5
|
|||
|
|||
Pivot Table (behind the scene)
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" roger@technology4unospamdotcodotuk wrote in message ... 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 "DHN" wrote in message ... 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" roger@technology4unospamdotcodotuk wrote in message news Hi Dai Not enough information. What does your data look like? What are you trying to achieve? -- Regards Roger Govier "DHN" wrote in message ... 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 __________ 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 |
#6
|
|||
|
|||
Pivot Table (behind the scene)
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 "DHN" wrote in message ... 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" roger@technology4unospamdotcodotuk wrote in message ... 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 "DHN" wrote in message ... 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" roger@technology4unospamdotcodotuk wrote in message news Hi Dai Not enough information. What does your data look like? What are you trying to achieve? -- Regards Roger Govier "DHN" wrote in message ... 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 __________ 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 __________ 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 |
#7
|
|||
|
|||
Pivot Table (behind the scene)
Excel 2007 PivotTable
Consolidate diverse Tables on multiple tabs with macro, then Pivot: http://c0444202.cdn.cloudfiles.racks.../01_04_10.xlsm |
#8
|
|||
|
|||
Pivot Table (behind the scene)
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" wrote in message ... Excel 2007 PivotTable Consolidate diverse Tables on multiple tabs with macro, then Pivot: http://c0444202.cdn.cloudfiles.racks.../01_04_10.xlsm |
#9
|
|||
|
|||
Pivot Table (behind the scene)
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.racks.../01_07_10.xlsm |
Thread Tools | |
Display Modes | |
|
|