Solved

Trying to populate a summary worksheet in excel from another worksheet

Posted on 2016-08-19
14
76 Views
Last Modified: 2016-09-08
I have a summary worksheet in excel I am trying to create, it is to be generated from a much more detailed server inventory worksheet. Within the inventory worksheet called 1-inventory I have a server name in column A, a Server Role in Column B, and a Severity level of the server in Column C. The severity level of the server in Column C sets the reboot schedule of those servers.  In Column G is labeled as Order, what this is, is a order as to which the server should be rebooted, this is per severity since these reboots happen on different days, 1 being the first server to reboot and the word Any meaning can be rebooted in any order.

On the Summary Page, I am looking to derive the following from the inventory page in this format:

Column A - (Low Priority) Server Name, Column B - (Low Priority) Reboot Order
Column C ( Medium Priority) Server name, Column D (Medium Priority) Reboot Order

I would also like the order of these servers shown on the summary page to be ordered by the reboot priority and only populate servers with the role with the word "Windows" in it, I am also ok to put 99 as a replacement for the word Any to keep the inventory column J as numerical for sorting purposes.

I have tried VLOOKUP and the closest I came was to return only 1 field, I am trying to populate the summary page as sort of an executive dash board and would need to fill all the cells in each column with the matching results. I am looking to do this for each severity role.

I tried to be as descriptive as possible and attached is a sample spreadsheet.

Any help is appreciated. Thanks!

sample1.xlsx
0
Comment
Question by:Steve Myers
  • 8
  • 6
14 Comments
 
LVL 7

Expert Comment

by:tomfarrar
ID: 41762949
Have you considered a pivot table?
0
 

Author Comment

by:Steve Myers
ID: 41763019
Yea but doesnt come out right unless im doing something wrong.... do you have a step by step on it to try? I am not too familiar with pivot tables...
0
 
LVL 7

Expert Comment

by:tomfarrar
ID: 41763113
I am not at a place where I can take a closer look at the moment, but I will look at tomorrow.  In the meanwhile you may get a solid answer from another expert.
0
 

Author Comment

by:Steve Myers
ID: 41763137
Ok thats fine, I would find it better to go the programming route instead of a pivot table though if possible :)
0
 
LVL 7

Expert Comment

by:tomfarrar
ID: 41763885
A couple of comments:

The data example provided did not cover all the items you were asking for.  "Medium Priority" was not included, and you did not speak to "Critical Priority".  I am guessing you didn't want to see the other fields in the summary report you were asking for

What I did was (as I interpreted what you were asking for) is to show you how a pivot table might give you what you are asking for.   I added some additional data which may, or may not, be valid.  I also change the data source to a table so that as the table expands or contracts, the pivot table is smart enough to know how many rows to include.

Though the format is not exactly as you requested it, I think it is giving you what you are asking for.  But this may require further discussion. I also gave you two different pivot tables with different ways the data could be shown.

Also if you want to try for the VBA solution only, you could close this question and repost it with emphasis in the title for VBA.  That is my thought...

File is attached.
EE.xlsx
0
 

Author Comment

by:Steve Myers
ID: 41765707
Ok let me clarify but that does look really good, I have a document with data matching that which represents a reboot schedule based on the severity level of the server basically by business impact.  So critical servers will reboot on Third Saturday 11pm for instance, the ones I dont care about are ones marked manual or other. I only really care about Low, Critical, Medium, and Essential. What I am trying to do is build a summary page on another worksheet that auto populates based on this technical list of servers because management feels they dont need to see ALL the information, the only thing they really need to see is: Server name, Server Role, notation, and critical level and sort these servers by the reboot order this way when I have a tech perform the reboot task that can also serve as a check list and ensure the servers are rebooted in proper order.  I need to have it list something like this:

Low Impact Servers - Reboot on This day
Server Listing     Server Role    Severity

Medium Impact Servers - Reboot on This day
Server Listing     Server Role    Severity

Essential Impact Servers - Reboot on This day
Server Listing     Server Role    Severity

Critical Impact Servers - Reboot on This day
Server Listing     Server Role    Severity

I hope this helps clarify things, I would need to store this summary on a separate worksheet this way I can just go and print the worksheet and hand it in and say heres what I am doing on this day to get approvals as needed, the data would need to update with changes on the full document in 1-inventory.

Thanks!
0
 
LVL 7

Expert Comment

by:tomfarrar
ID: 41766137
If you could take the spreadsheet I gave you and the sample you originally provided and put more data on there that would be representative of all possibilities of what could hap.pen, then visually it would help me.  It would also give you a better picture of what you expect.  As far as having on a different page, that is no problem.  I only put it on the same page so I could visually look at the summary result and the detail behind it.  - Tom
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

Author Comment

by:Steve Myers
ID: 41766148
Actually your example is very close the only problem is, it should first sort by the Severity Priority IE Critical etc, then by the reboot order, 1 being on top and descending downward to the lower priority, each pivot being its own list of servers, for example:

Low Priority (Pivot)
Server Name           Server Role        Severity            Notes             Order (Headers)
Server1                     Windows: AD     Critical             whatever        1
Server3
Server4

Medium Priority (Pivot)
Server Name           Server Role        Severity            Notes             Order (Headers)
Server2                     Windows: AD     Medium             whatever        1
Server5
Server6

And so on.

I do like the example you provided though, I hope this clarifies good :)
0
 
LVL 7

Expert Comment

by:tomfarrar
ID: 41774067
Not tracking.  What field gives you Low Priority, Medium Priority, etc?  It appears that is what severity is doing.  I don't believe the last example you gave me tracks well with the original data.  The pivot table can be sorted pretty much any way you want it to.  If you provide me a examples with the real data, then I think we can finish this out.  - Tom
0
 

Author Comment

by:Steve Myers
ID: 41774951
The severity is the priority is the reboot priority, so in each group of importance levels of the server (Severity) there is an order associated to it, this order would be 1,2,3,4,5 and so on with Any (Or 99) as any order following the others with lower numbers, a 1 means this server must be rebooted BEFORE any other ones.
0
 

Author Comment

by:Steve Myers
ID: 41778357
Hello, any follow up?
0
 
LVL 7

Accepted Solution

by:
tomfarrar earned 500 total points
ID: 41783966
Try this one on, Steve.  Within the pivot table you can change the sorting by where you position the fields in the pivot, left to right in general.  If you don't like the way the pivot is showing data, you can manual change the order (I did this with Severity: changed the order to Critical, Medium, Low and Other) by moving the names around.  Once you have moved the names to the order you want, the pivot table will retain that view when updated.  See example.
EE-Late.xlsx
0
 

Author Comment

by:Steve Myers
ID: 41790400
I am going to check it out further it looks good so far so i will award points and close in the meantime, thanks for the help!
0
 

Author Closing Comment

by:Steve Myers
ID: 41790402
Thank you for the help
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now