Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!
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!
sample1.xlsx
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
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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.
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.
EE.xlsx
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
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!
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!
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
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 :)
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 :)
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
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.
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
EE-Late.xlsx
Premium Content
You need an Expert Office subscription to comment.Start Free Trial