Glenn said:

With some helper cells. Name your range "Data". Name a blank worksheet

"Helper". Put the following array formula (commit with

CTRL+SHIFT+ENTER) in Helper!A1:

=SMALL((Data="")*9^9+ROW(Data),ROW(1:1))-ROW(Data)+1

Put this array formula (again, commit with CTRL+SHIFT+ENTER) in Helper!B1:

=SMALL((INDIRECT("'"&CELL("filename",Data)&"'!R"&A1+MIN(ROW(Data))-1&

"C"&MIN(COLUMN(Data))&":R"&A1+MIN(ROW(Data))-1&"C"&MIN(COLUMN(Data))+

COLUMNS(Data)-1,0)="")*9^9+COLUMN(Data),COUNTIF($A$1:A1,A1))-COLUMN(Data)+1

Copy each down for as many rows as there are total used cells in "Data".

In the cell where you want your list to start, put this "normal" formula:

=IF(ISNUMBER(ERROR.TYPE(Helper!A1)),"",

IF(OR(Helper!A1="",Helper!A1>ROWS(Data)),"",

INDEX(Data,Helper!A1,Helper!B1)))

A couple of follow-ups:

The named range "Data" should include only the values you are looking to list,

not the row or column headers, if present.

If the first two formulas above ended up with 99's in them when copied to Excel,

it should have been 9 raised to the 9th power. Instead, you could replace them

with "ROWS(Data)", without the quotes but definitely with the "S".

If you have headers on your rows and columns in "Data" and want to know where

the result was within "Data", use the following "normal" formula:

=IF(B19<>"","ROW ["&INDEX(INDIRECT("R"&ROW(Data)&"C"&COLUMN(Data)-1&

":R"&ROW(Data)+ROWS(Data)-1&"C"&COLUMN(Data)-1,FALSE),Helper!A1)&

"] - COLUMN ["&INDEX(INDIRECT("R"&ROW(Data)-1&"C"&COLUMN(Data)&

":R"&ROW(Data)-1&"C"&COLUMN(Data)+COLUMNS(Data)-1,FALSE),,Helper!B1)&"]","")

This assumes that the first entry of your result list is B19. Adjust as needed.