asked on

Vlookup or Pivot Table

Hello,
I've been tasked with something that not really in my area of expertise. I'm primarily a database developer.

I need to provide the following information from the details tab of the attached workbook
1. A list of the applications included in the Application Retirement project
2. A list of the servers that are impacted by the Application Retirement project
3. The list of servers that could be removed from the Bank's inventory if the applications listed were retired.
4. What are the # of servers and AITs reduced per LOB?
5. What is the impact of server reduction per location?
6. Show the impact to the Data Center Closure Initiative for 2011 vs. 2030
7. The manager would like an easy way to swap out the data in the Details tab and refresh the analysis

Now I've worked with pivot tables but I'm trying to decide if I need to go the vlookup route. Perhaps I should create multiple tabs on the worksheet. I'd like some advice on the best approach to take. I can do this via a database approach, but the manager wants an Excel Based approach

Project-Impact.xlsx

Juan Velasquez

ASKER

It's not homework. The instruction sheet is a explanation that I received from the manager. So I'll thank you not to accuse me of submitting homework. By the way, if you had BOTHERED to read my questiion more CAREFULLY, you would have noticed that I was asking for advice on the approach to take NOT ON THE CODING.

Juan Velasquez

ASKER

" I'd like some advice on the best approach to take" NOTE: I SAID ADVICE!!

Juan Velasquez

ASKER

To Teylyn:

Do you have any more comments you'd like to share with me?

Ingeborg Hawighorst (Microsoft MVP / EE MVE)

Well, seeing that the objective is data aggregation and summarising, and keeping in mind that Vlookup is designed to return a single value from a list, I'd hazard a guess that Vlookup will not provide a suitable approach, whereas with pivot tables, the data can quickly be sliced and diced in many ways.

cheers, teylyn

Juan Velasquez

ASKER

That's the solution I came up with and am implementing. When I complete it, I'll post it. Do me a favor. Don't comment on it.

ASKER CERTIFIED SOLUTION

byundt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Juan Velasquez

ASKER

Thanks byundt:,

Actually I've just completed doing exactly that. My next step is to see if I can create a section on the details pages called for want of a better word "Analysis Results" . Analysis Result would draw its data from each of the pivot tables. I envision after a user makes a change in the details section, the user could refresh the workbook and see the results in the Analysis Results sections

byundt

If the user makes a change in the Details worksheet, then each PivotTable needs to be refreshed. You may want to record a macro to perform the refresh on one of the PivotTables. You can then clone the recorded code for each of the other PivotTables.

Brad

Juan Velasquez

ASKER

I wonder if I can just put a command button on the details worksheet so that when it is clicked I could execute code to refresh each pivot table

Anthony Perkins

>>Zones: SQL Server 2008, Microsoft Excel Spreadsheet Software<<
So I gather you are not looking for a T-SQL solution. If not, then please request that the SQL Server 2008 zone be removed. It does not apply.

Juan Velasquez

ASKER

Sorry about that, I hadn't noticed. I'll take care of it

byundt

You can put a button on the Details worksheet to call a macro. I used one from the Forms toolbar, and put the macro in a regular module sheet.

Sub RefreshPivotTables()
Dim ws As Worksheet
Dim i As Long, n As Long
For Each ws In ActiveWorkbook.Worksheets
    n = ws.PivotTables.Count
    If n > 0 Then
        For i = 1 To n
            ws.PivotTables(i).PivotCache.Refresh
        Next
    End If
Next
End Sub

Open in new window

Instead of using a button, I could alternatively have called the macro whenever the user left the Details worksheet after updating it. Such a Worksheet_Deactivate event macro would go in the code pane of the Details worksheet, and would look like:

Private Sub Worksheet_Deactivate()
RefreshPivotTables
End Sub

Open in new window

Note how the event macro calls the same sub as the Forms toolbar command button.

The attached workbook shows both methods in action.

Project-ImpactQ27318181.xlsm

Juan Velasquez

ASKER

To byundt:
You wrote the following
=B2-COUNTIF(Table1[[#All],[Server Name]],A2)

I'm not sure but unless I'm not understanding something shouldn't that be a DCount function since the arguments for CountIF is
CountIF(Range, Criteria).

byundt

I built the COUNTIF formula by selecting cells A1:A4431. The formula wizard then translated that into a table reference as posted (and as shown in my sample workbook).

DCOUNT would require a different (and more cumbersome) layout, with the filter criteria appearing above the data. I never use database functions like DCOUNT because the alternatives are just as capable and easier for most Askers to understand.

Juan Velasquez

ASKER

Thanks for the clarification

Juan Velasquez

ASKER

Thanks for the assist, I'm particularly grateful for your confirmation of the use of Pivot tables as the best approach.