Cross sheet Formula to pull in column value

Hello,

I have a Master Server Inventory sheet with columns such as Server Type, Server Name, Recovery Method, and several other columns with common data that has to be collected for all servers. I need to create a variety of separate sheets with detailed configuration data about each server. I want to reference and pull over the server names and server types into each detailed config sheet.

So I have a config sheet called Intel Network Interface Configuration Sheet. I need to populate this config sheet with all server names from the Master Inventory that have a Server Type = Intel in the master inventory.

What's the best formula to do this cross sheet formula? Index/Match? Could you provide an example.

The server name columns in the master inventory and config sheets will all share the same column name of "Server Name"

Thanks in advance.

master test setup inventory.PNG

intel conf sheet.PNG

Find more posts tagged with

Formulas

IT & Operations

Project Management

Cell linking

Comments

Paul Newcome

So you are basically trying to look at your master sheet and pull all server names into a sheet based on server type? I assume there could be multiple names per type?

Richard Kendall

Hi Paul, thanks for replying. You're correct. I found a video on youtube on how to use vlookup and so I was able to accomplish my first goal of searching the master sheet for the [server name] and return the server type for that server name. Works fine and the formula is below.

=VLOOKUP([Server Name]1, {Customer Intel Server Inventory Range 1}, 2, false)

However, I now need to figure out the best way to return multiple pieces of data for that server name, namely Server Type AND Server OS. Server Type is working not sure best way to pull Server OS too in the same formula.

Also not sure if Index/Match is better based on reading other posts. I couldn't get an index match formula to work for the cross reference sheet.

Thanks for your help.

Rick

Paul Newcome

Ok. It's making sense. INDEX/MATCH is MUCH better than VLOOKUP. I'll get into more detail with that once we determine exactly how we want to structure your solution.

The biggest question would be this...

Will there be multiple server names per type?

Basically... When you are setting up your sheets by server type, will you need to pull multiple rows from the master sheet?

Richard Kendall

Hi Paul -

Yes, it will be very common for a Server Type to have between 1 and hundreds of different server names. The Master Inventory will have server name, server os, server type columns among other columns. The disk and network interface sheets will have Server Name, Server OS, and Server type columns (same as master inventory). Right now I have vlookup pulling in the Server OS and Server Type columns from the Master Inventory when a user types in the name of a server into a Server Name column in the Disk and network interface sheets. Would be nice if I could have a dynamic drop down based on the server names in the master sheet but that doesn't seem to exist (i know i can hard code server names in drop down column OR link the column from Inventory but neither of these options work for the server name in the Disk and Network sheets. So I have folks retype the name of the server for each row. The same server name can be used on multiple consecutive rows based on the number of DRIVE Letters and NICs needed for each server. (sorry about the tangent regarding server names).

Vlookup seems to work fine pulling in server OS and Type column info from Main Inventory to Disk and Network Interface sheets, however, I want to architect this the best way from the beginning.

Thanks very much.

Rick Kendall

Paul Newcome

Sorry for all of the questions. I just want to make sure we are all clear on the details before attempting a build.

I will use your screenshots from your original post for reference...

You want to build a sheet for all IBM Power8 servers. This sheet will reference back to your master sheet and list off all of the server names for the corresponding type. You then want to pull from the master all relevant server data based on that specific server.

You will then have another sheet doing the same thing for IBM Power7 servers, so on and so forth.

Does this sound correct so far?

What is the purpose of these server type sheets? Are they used to log specific events, or are they more for inventory purposes?

I do have other questions, but they are going to be based off of your responses to the above.

Sign in to join the conversation!

Quick Links

Trending discussions

Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?
Need Help with If, Index, Collect
Please help, I have a few of these to do. I have a nested IF formula. The first part is an INDEX/MATCH and works as a standalone. The next few are INDEX/COLLECT. These are not working as standalone or in the nested version (Unparsable Error). Any ideas? =IF(CONTAINS(“A/V”, [Vendor Type]@row ), INDEX(COLLECT({Date Secured…