Indexing the sheets of another workbook

Copper Contributor

Hello! I'm trying to make a spreadsheet that automatically tells me all of the SKUs from each sheet of different workbooks.

In the real life scenario there are about 10 different files which are different server form factors

Each file has about 25 sheets made of different server models

Then each sheet has about 15 different SKUs which are examples of servers with different specs (I didn't include this bit on the example as it's not very helpful)

I want to be able to type in the name of the file I'd like to index in B1, then below B1 it will create a list of all the different sheets within that file. For example, in B1 I typed in "Rack Servers". This then looks inside '[Rack Servers.xlsx] and makes a list of the different sheets

That's the bit that has me stumped. I know it's possible to index sheet names if the list is in the same workbook as the sheets I'm trying to list, this can be done through formulas or macros. But is it possible to ask for a list outside the workbook?

After that, my stock page will columnize what the list has returned with the list items as the column headers. It will then concatenate the column headers with a cell reference to call the SKU.

This can be exampled with:

B1 is manually typed in with "Rack Servers".

A1 says "File:" as a header to what will be typed in B1

A2:A27 is a count from 1 - 26 just to create an order to the list

B2:B5 return R520,R620,R720 and R820 respectively.

D1 = B2, E1 = B3, F1 = B4, G1 = B5

Then for the rest of the sheet (going diagonally from D2 to G5 to give an idea of the formula change)

D2 = "=INDIRECT(CONCATENATE("'[",$B$1,".xlsx]",D$1,"'!$A",$A2))" returning SVR-R520-001-S

E3 = "=INDIRECT(CONCATENATE("'[",$B$1,".xlsx]",E$1,"'!$A",$A3))" returning SVR-R620-002-S

F3 = "=INDIRECT(CONCATENATE("'[",$B$1,".xlsx]",F$1,"'!$A",$A4))" returning SVR-R720-003-S

G4= "=INDIRECT(CONCATENATE("'[",$B$1,".xlsx]",G$1,"'!$A",$A5))" returning SVR-R820-004-S

to break down this formula

=INDIRECT(CONCATENATE("'[",$B$1,".xlsx]",G$1,"'!$A",$A5))

=INDIRECT(CONCATENATE("'[", THE NAME OF THE FILE,".xlsx]", THE FORM FACTOR,"'!$A", THE NUMBER OF THE SKU))

The length of the B2 onwards list will grow depending on the amount of sheets,

The amount of columns from D going right will grow depending on the amount of results from the list,

The amount of populated rows in the table will grow depending on the amount of SKUs within the page referenced by Row 1

0 Replies