Match Hostname to find data from multiple worksheets

[suchetherrah]

Hi,

Management has given a challenge to find the new IP address and it's information matching with Hostname.

The challenge is the team as created various worksheet names which is really massive and has different name too in 1 file which I couldn't even list them.
If i use vlookup or index/match, I need to list all the worksheet names, correct?

Attached is the sample file.

Can anyone please advise?

[JohnTopley]

You need to ensure All the tabs have data in the same column: you don't have "Original IP address" in 2 of the tabs so please add so we can have consistent "lookups".

[suchetherrah]

Understood, I have removed the columns accordingly and reattached the file.
All the IP addresses which I need to identify are in Column A.

[JohnTopley]

With standard format on all sheets:

in RESULTS

in G5

Please Login or Register  to view this content.

in C6

Please Login or Register  to view this content.

copy across to F

in H6

Please Login or Register  to view this content.

[JohnTopley]

My last post has IP address added: you will need to adjust formulae if it removed as per your last post.

Change $C$2:$C$1000 to $B$2:$B$1000

[suchetherrah]

Hi John,

Thank you, John. The challenge is the team has created about 40 to 50 sheets (Not sure how many) which I have difficulty in identifying one by one.
Is there a way to identify the active sheets using a formula?

I also need your advise where the team has given me 3 workbooks which has similar field names to locate the IP address from either one of the workbooks.
(Location1 IP List.xlsx, Location2 IP List.xlsx. Location2 IP List.xlsx).

It will be a great help coz we are organizing the inventory of the servers.
Really need your advise and assistance, sir.

[JohnTopley]

Multiple Sheets in a Workbook are catered for by the "Sheetlist" named range. I forgot to mention this in my previous post.

[suchetherrah]

So I have the manually identify and create the sheetlist as legend?
But I have 40 to 50 active worksheets in each file.

Is there a formula to identify the sheetlist?

[suchetherrah]

Any advise, on how to identify the 40 to 50 Sheetlist names, please?

[JohnTopley]

No formula I am aware of: you could use VBA to build the list.


list sheet names in column A

Please Login or Register  to view this content.

[suchetherrah]

The above solution is working for a single workbook. If I need to find the IP address from 3 different workbooks, should use VBA coding?

Should I open a different thread?