Auto-populate a range with selected rows from a second range... But the NIGHTMARISH way!

Hi all,

Only my second post, so bear with me, I'll try hard to be clear. First, the setup:

Sheet2:
- 3 columns: three-letter code, material name, material type

Sheet1:
- rows of data for individual stock items with 22 columns (A-V) worth of descriptive fields.
- column C is for typing one of the three-letter codes in Sheet2
- columns D (material name) and B (material type) auto-populate based on the data in Sheet2 with the formula 'IF(ISBLANK(C2),"",VLOOKUP(C2,Table5[#All],3,FALSE))'

Now for the hat trick, currently beyond my limited excel-based powers:

Can I create a Sheet3 with a table/range that shows ONLY the rows of Sheet1 with stock items of ONE material, as shown in Sheet!2?

I know... Sorry. Not easy for me anyway. Any help would be greatly appreciated.

Please read the yellow banner at the top of the page and supply a sample workbook. I'm assuming you're still using V2016?

I... am still trying to figure out how to do that, I'm that much of a newbie I'm affraid. Office 365 is the version, and I'll keep trying, I'll get there.

...OK, I uploaded a sample workbook, just don't know how to link you to it?

To upload a sample workbook, as your typing in the "reply" box (like I am now), click on the GO ADVANCED button below, then scroll down to MANAGE ATTACHMENTS and click again. Then scroll down and click the "Manage Attachments" link. In the next window, click on the "Choose File" button at the top left of the page. Find your file and click OPEN. Then click on the "Upload" botton. Once done, click the "Close this Window" button at the top-right of screen. Then submit your reply.

Allright, hope this is it then!

MAIN sheet is where all entries will be made

DATA sheet is where all materials will be kept up to date

HEARTH, GRANITE & QUARTZ sheets should look like I have them... And ideally should auto-populate from the MAIN sheet

...I hope that makes sense.

For HEARTH, use this:

=FILTER(MAIN_table,MAIN_table[MATERIAL]="Hearth")

Then change appropriately for the other sheets.

Thank you, but the formula results in a #SPILL! error. Any idea why?

Yes. 2 things:

1. On each sheet, you need to convert the Table to a Range (do this on the "Table Design" Menu).

2. Delete all your data (under the headings and put my formula in cell A2. This is a dynamic array formula so it "spills" out the result across all the cells needed.

See Attached.

Awesome! I was getting frustrated with convoluted formulas, thank you for the help.