Formula to show what items are being used on a range from a table list

You already have that information in the range where it is used so why duplicate it?

If needed, just duplicate it with =IF(A1="","",A1)

Change A1 to match the first cell of your actual range then drag as needed.

Yea but that formula only shows the number values of how many items is used, and what I need is column that shows item actual names which are being used.

Originally Posted by jason.b75

You already have that information in the range where it is used so why duplicate it?

If needed, just duplicate it with =IF(A1="","",A1)

Change A1 to match the first cell of your actual range then drag as needed.

I get value error with this formula when I select range such as E8:J16, I need it to go down and to the side.

Could you attach a sample workbook please (not a screen capture). Remembering to include a mock up up the expected results.

edit:-

The error is because that formula is not meant to take a range, only a single cell.

See if this does as expected.

=IFERROR(INDEX(M:M,AGGREGATE(15,6,ROW($M$23:$M$120)/($P$22:$P$120>0),ROWS($O$22:O22))),"")

I noticed that the count formulas in column P are only looking at the first 2 green tables, not all 5 of them, so that will need adjusting if it is not correct.

It works great! Just what I needed, thank you. One last question, if I accidentally use the same item name twice or more times and it gets duplicated, can I use some kind of formula which would show me only the duplicated names in another column?

Never mind, I found it myself. Thanks for your help!

If you change the definition of the named range, mzones from

=Sheet1!$M$23:$M$94

to the formula,

=Sheet1!$O$21:INDEX(Sheet1!$O$21:$O$104,COUNTIF(Sheet1!$O$21:$O$104,"?*"))

Then the data validation list will only show unused names, preventing you from using the same name twice.