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
chtullu135Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
byundtConnect With a Mentor Commented:
I suggest creating a Table using the Insert...Table menu item on the Excel ribbon. You can then create multiple PivotTables using that Table as the source. If your manager changes the data, then you would just refresh each of the PivotTables to see the effect.

It may be that I'm not understanding the ground rules for this assignment, but it appears that some servers are running more than one app--and that not all of those apps are on the retirement list. If so, the server cannot be retired. To account for this possibility, I added a column to the table with a formula like:
=B2-COUNTIF(Table1[[#All],[Server Name]],A2)                  'I called my table Table1. Note how this formula uses the table name & header label to refer to the field

I also added a column for the retirement year, using a VLOOKUP formula:
=VLOOKUP(C2,'Data Center Closure Targets'!$A$2:$B$33,2,FALSE)

Using this groundwork, you can then build separate PivotTables to answer each of the questions. I suggest building these PivotTables each on a separate worksheet.  You will be putting the Server Name in both the Row and Value fields. If you put LOB or App. AIT Code to the left of the Server Name in the Row field, then you get a listing (and count) of servers by LOB or AIT.
0
 
chtullu135Author Commented:
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.
0
 
chtullu135Author Commented:
" I'd like some advice on the best approach to take"  NOTE:  I SAID ADVICE!!
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
chtullu135Author Commented:

To Teylyn:

Do you have any more comments you'd like to share with me?
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
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
0
 
chtullu135Author Commented:
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.
0
 
chtullu135Author Commented:
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
0
 
byundtCommented:
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
0
 
chtullu135Author Commented:
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
0
 
Anthony PerkinsCommented:
>>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.
0
 
chtullu135Author Commented:
Sorry about that,  I hadn't noticed.  I'll take care of it
0
 
byundtCommented:
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
0
 
chtullu135Author Commented:
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).
0
 
byundtCommented:
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.
0
 
chtullu135Author Commented:
Thanks for the clarification
0
 
chtullu135Author Commented:
Thanks for the assist, I'm particularly grateful for your confirmation of the use of Pivot tables as the best approach.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.