Trying to populate a summary worksheet in excel from another worksheet

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!

Steve MyersAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Have you considered a pivot table?
Steve MyersAuthor Commented:
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...
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.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Steve MyersAuthor Commented:
Ok thats fine, I would find it better to go the programming route instead of a pivot table though if possible :)
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.
Steve MyersAuthor Commented:
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.

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
Steve MyersAuthor Commented:
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

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

And so on.

I do like the example you provided though, I hope this clarifies good :)
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
Steve MyersAuthor Commented:
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.
Steve MyersAuthor Commented:
Hello, any follow up?
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Steve MyersAuthor Commented:
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!
Steve MyersAuthor Commented:
Thank you for the help
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.